Multiple Column Sorting

C

chinoyvr

Using Excel 2003, want to sort example listed below...there are no headers...

25 20 15 10 5
24 19 14 9 4
23 18 13 8 3
22 17 12 7 2
21 16 11 6 1

Basically, each number is on a different row and column orderly.
Is there a way to sort all of it and put it in proper order from lowest
number and up first by column, then by row...so that the outcome would look
like;

1 6 11 16 21
2 7 12 17 22
3 8 13 18 23
4 9 14 19 24
5 10 15 20 25

The example I used is small as the spreadsheet I work on is about 20 columns
wide and 29 rows deep.
 
M

Max

With your sample data pasted into A1:E5
I selected the range A1:E5, then did:
Data > Sort > By Col A (ascending),
then Data > Sort > Options > Check: Sort Left to Right > OK out
and it returned the results you seek

Tried reversing the steps order, ie:
Sort L-to-R 1st, then by col A, ascending,
and it returned ok as well
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
 
S

ShaneDevenshire

Hi,

For a more general solution try this. Assume that your data is in the range
A1:E5

Enter the following formula in a cell and copy it down five rows and over
five columns:

=SMALL($A$1:$E$5,ROW(A1)+5*(COLUMN(A1)-1))

Note this will work even if the number are not well ordered as in your
example. The sorting solution would fail if swapped the 1 and the 25, but
this formula will not. You can adjust the arguments of the formula to
address other ranges.

If this helps, please click the Yes button.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top