How do I sort by row instead of by column?

[Guest]

In Excel 2003, I have a range 50 columns wide and 600 rows tall. Row 1 has
the column headings, while Rows 2-600 contain the data pertaining to the
column heading shown in Row 1.

Now I want to sort the entire range according to the column headings, so
that Location 1, which now occupies the first 600 rows of Column M, will end
up in Column A; Location 2, which now occupies 600 rows of Column AE, will
end up in Column B; and so on, each column carrying along with it the cells
under that column heading.

The Data > Sort menu command will sort by column, but how do I sort by using
Row 1 as my index?

More to the point, why has Excel been designed to sort rows according to
column, but not sort columns according to row?

 

[JulieD]

Hi

it can be done
i. insert a new row directly above your data
ii. number each column in the order that you want to see it in this row
e.g.
......A................B............C
1...3................2..............1
2...FName...LName........Title

iii. now click in a cell in your data range and choose data / sort
iv. click on OPTIONS button
v. choose SORT LEFT TO RIGHT
vi. click OK, the first Sort by field should now say Row 1 (or similar)
vii. click OK to sort

(of course do this on a backup of your workbook)

Hope this helps
Cheers
julieD

 

[Guest]

Thanks, your method does indeed work.

But unless I am misunderstanding your method, essentially it requires me
(not Excel) to do all the work of sorting, because I must scan all 50
columns, decide which one should come first alphabetically, then put a "1" in
the cell above it; scan the remaining 49 columns, decide which should come
second, enter a "2" in the cell above it; and so on, through all 50 columns.

As long as I'm going to all that trouble, I may as well sort the columns
manually by inserting a new blank column to the left of my range, then once I
locate the column that should come first, simply cut and paste it into the
new column, rather than enter a "1" at the top, and continue this method
through the other 49 columns.

Do these two methods essentially boil down to the same amount of work on the
user's part?

 

[JulieD]

Hi

didn't realise that you wanted them alphabetically - in that case you can
skip items i & ii and just start at iii

Cheers
JulieD

 

[Guest]

Excellent. Thank you so much!

Hi

didn't realise that you wanted them alphabetically - in that case you can
skip items i & ii and just start at iii

Cheers
JulieD

 

[JulieD]

you're welcome

Excellent. Thank you so much!

 

[Guest]

Well, what that method is good for; however, is for cases when one needs to
enumerate columns as fields. Whenever the rank order of fields need to be
redone, all one has to do is go into the "rank" row and change the numbers
around. Once renumered, then redo the sort order from left to right! Then
the fields that are preferred to be top order then get moved further to the
leftmost edge of the worksheet.

 

[Guest]

In Excel 2003, I have a range 50 columns wide and 600 rows tall. Row 1 has
the column headings, while Rows 2-600 contain the data pertaining to the
column heading shown in Row 1.

Now I want to sort the entire range according to the column headings, so
that Location 1, which now occupies the first 600 rows of Column M, will end
up in Column A; Location 2, which now occupies 600 rows of Column AE, will
end up in Column B; and so on, each column carrying along with it the cells
under that column heading.

The Data > Sort menu command will sort by column, but how do I sort by using
Row 1 as my index?

More to the point, why has Excel been designed to sort rows according to
column, but not sort columns according to row?