Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I sort by row instead of by column?
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? |
#2
|
|||
|
|||
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 "PercivalMound" wrote in message ... 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? |
#3
|
|||
|
|||
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" wrote: 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 "PercivalMound" wrote in message ... 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? |
#4
|
|||
|
|||
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 "PercivalMound" wrote in message ... 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" wrote: 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 "PercivalMound" wrote in message ... 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? |
#5
|
|||
|
|||
Excellent. Thank you so much!
"JulieD" wrote: 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 "PercivalMound" wrote in message ... 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" wrote: 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 "PercivalMound" wrote in message ... 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? |
#6
|
|||
|
|||
you're welcome
"PercivalMound" wrote in message ... Excellent. Thank you so much! "JulieD" wrote: 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 "PercivalMound" wrote in message ... 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" wrote: 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 "PercivalMound" wrote in message ... 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? |
#7
|
|||
|
|||
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. :) "JulieD" wrote: 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 "PercivalMound" wrote in message ... 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" wrote: 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 "PercivalMound" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I sort by row instead of by column?
"PercivalMound" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I sort a column with several categories (ie red and blue) | Excel Worksheet Functions | |||
How do I sort a column of street number/street name by the stree. | Excel Worksheet Functions | |||
"-" ignored in sort | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) | |||
How do I sort a column of formulas in Excel? | Excel Discussion (Misc queries) |