I have a worksheet that I need sorted, the only problem is Column A is connected to 30 other columns. I need column A to be sorted, but I also need all the row data in that column to follow suit. To better put it, I have a tree branch that I wanted moved, but I want all the leaves on that branch to move with it. Have a look at the excel file, you will have a better understanding once you see it. This is a condense file, the one I need sorted is 12000 lines.
Thanks in advance for helping
The simple, universal solution is to not merge cells in Excel. If you need to duplicate data, do so.
Entia non sunt multiplicanda sine necessitate
If it was that simple, I would not have asked. The output file comes merged. Any solutions? Thanks.
Unmerge all cells
Select the col A, Ctrl+G (goto), Special, Blanks
In the formula bar type = then press the up arrow key, then press and hold Ctrl, then press Enter. Now what were blanks in col A duplicate the cell above.
Select col A (again), copy, paste special, values
Sort everything by col A.
thanks for the response, I got to the part where you type =, when I did all that, I get "=" in column A. What you mean what were blanks in Col A duplicate the cell above? Sorry, not quite following.
If you select A2 and type = in the formula bar, and then press the up arrow key, the formula changes to =A1. You're doing that simultaneously in all blank cells.
To expound on what shg said.
1. Unmerge all cells (this won't work unless all cells are unmerged)
2. Select the col A,
3. Ctrl+G (goto), (F5 works too)
4. Click on Special then Blanks
At this point all the blank cells (to the bottom of your range) in Column A will be selected.
5. Put your cursor in the Formula box (white box on tool bar where you usually see your formula) and type = then Arrow Up. (if this doesn't complete the formula, just type in =A1 in the formula box)
6. Hit CNTRL + ENTER
That should fill in all those cells.
7. Copy, and Paste Special > Values.
That help?
Last edited by ChemistB; 03-25-2011 at 03:43 PM.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
When I do Copy, and Paste Special > Values, it goes "That command cannot be used on multiple selections."
Capiche ?Originally Posted by shg
Yes, I did all that but when I sort, only column A gets sorted, and I expand the field.
Yes, I did all that but when I sort, only column A gets sorted, ...Every step counts, Sarah.
hi, I tried all your steps, i run into an error when I get to "Select col A (again), copy, paste special, values"
"That command cannot be used on multiple selections."
Do you have an autofilter on? or hidden rows? Everything must be showing when you do these steps.
I do not have any autofilters or hidden rows, too bad the excel file I uploaded was already sorted, otherwise you guys can test it.
Worked for me as described in both Excel 2000 and 2007.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
That means click the header at the top of the column, so that the entire column is selected. You must do this, because after selecting the blanks, you have a disjoint selection, which will give the error you describe.
Please Login or Register to view this content.
Yes, it worked, thank you, you guys are amazing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks