MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sorting


Posted by ross on November 08, 2001 5:55 AM

alrighty,

can i sort my rows of data based on one column? ie will all the row move or just the column i sort on, i want the whole rec to move......
having done that can i then sorted the "sorted" data based on another column eg
a 3
d 2
a 2
c 4
b 1

then first get
a 3
a 2
b 1
c 4
d 2

and finally:
a 2
a 3
b 1
c 4
d 2


plsssssssssss help its driving me mad!!!!!


Posted by Tom Urtis on November 08, 2001 6:20 AM

There are probably more elegant ways to do this, but if your data all looks the same (one letter and one number), try this:

To start, your second wish is easy, it's a straight-up sort with no column header, in ascending order.

To achieve your first wish:

(1) Assuming the data starts in A2, in B2 enter =LEFT(A2,1), and in C2 enter =RIGHT(A2,1).
(2) Copy the formulas down as needed.
(3) Highlight the B and C column formula range, click Edit > Copy, Edit > Paste Special Values.
(4) Now select range A to C (including the original data and the separated data), click Data > Sort, sort by column B ascending, and column C descending, again, with the No Header Row button selected. Delete the contents of B and C.

Tom Urtis

Posted by Juan Pablo on November 08, 2001 6:33 AM

If you want the entire record sorted, and it is (taking the example) located in A2:B6 (Without headers), select this range, go to Data - Sort,

Select Column A from first list, Ascending, Column B from second, Ascending, and make sure that "Headers" is in NO. That way you'll get the expected results.

Juan Pablo There are probably more elegant ways to do this, but if your data all looks the same (one letter and one number), try this: To start, your second wish is easy, it's a straight-up sort with no column header, in ascending order. To achieve your first wish: (1) Assuming the data starts in A2, in B2 enter =LEFT(A2,1), and in C2 enter =RIGHT(A2,1).