Forum home » Delegate support and help forum » Microsoft Excel Training and help » Sorting
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Sorting
Resolved · Medium Priority · Version 2010
Reply Thu 8 Dec 2011, 11:39Delegate Michael said...
Michael has attended:
Excel Intermediate course
Sorting
Is it possible to sort columns where the cells are merged? We have a program that we use which exports data to excel. It always exports the data with merged cells. I cannot then sort the columns as the cells are merged - any ideas?
For upcoming training course dates see: Pricing & availability
RE: Sorting
Hello Michael,
Hope you enjoyed your Microsoft Excel Intermediate course with Best STL.
Thank you for your question regarding sorting when you have merged cells.
It is impossible to sort / filter data where there are merged cells within the data. I would select the columns of data where the merged cells are to be found and then click the Merge & Center button to remove the merged cells. This does leave you with some blank cells here and there so you have to proceed with this method very carefully... and then make whatever adjustments are necessary. Sometimes it is useful to put the data with merged cells into a separate sheet, make the adjustments and then move the corrected data back to the original sheet.
Try this out!
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Fri 9 Dec 2011, 14:05Delegate Michael said...
RE: Sorting
Hi Rodney
Unfortunately this doesn't resolve my query. The suggestion that you have made is what I currently do but I was hoping to find a quick solution to this. Sometimes the programme we export stuff from can export information with thousands of lines of merged data in columns.
I was hoping to find a way to un-merge these cells quickly without manually having to go through each one and do it one at a time.
If this can't be done then never mind!
Mike
RE: Sorting
Hello Michael,
In my explanation I did not say you must unmerge the cells one at a time. You can select the entire sheet if you want and then click the Merge and center button. My advice was to select the columns which contained the merged cells and then unmerge.
Give it a try!
I hope this resolves your question. If it has, please mark this question as resolved.
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Mon 12 Dec 2011, 08:43Delegate Michael said...
RE: Sorting
When saying you have to do it one at a time, I meant that once you have highlighted the section that needs to be unmerged (this may be the whole document but for me is usually just one column) and clicked the merge and centre button, you are left with a lot of blank cells. You then have to copy the cell above these blank cells into the blank cells below. This is the bit that needs to be done one at a time and can be very very laborious if you have thousands of rows to go through!
I understand there is no solution to this (which seems a little crazy considering the seeming lack of complexity involved) and I will plod on with unmerging and copying!
Thanks for your help and advice. I will now mark this resolved (insomuch that I am aware there is no solution!).
Regards
Mike
RE: Sorting
Hello Michael,
There is a way to copy the cell above a blank cell into the blank cell. This can be done all at once.
First, select the column containing the blank cells. Then press CTRL + G and click the Special button. On the left of the dialog box select Blanks and click OK. This action will select all the blank cells in the column. Then press = followed by the up-arrow and then hold the CTRL key down and press Enter. If you do this correctly, all the blank cells will be replaced by the cell above.
Hopefully this will work for you.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Tue 13 Dec 2011, 08:48Delegate Michael said...
RE: Sorting
That's absolutely brilliant, thank you - just what I was looking forward!
Mike
|