|
|
| |
 Question |
|
| |
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? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
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" <PercivalMound@discussions.microsoft.com> wrote in message
news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
> 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?
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <PercivalMound@discussions.microsoft.com> wrote in message
> news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
> > 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?
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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" <PercivalMound@discussions.microsoft.com> wrote in message
news:8AA29AA0-55D7-45E8-9F29-A19DDDA13F10@microsoft.com...
> 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" <PercivalMound@discussions.microsoft.com> wrote in
>> message
>> news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
>> > 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?
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <PercivalMound@discussions.microsoft.com> wrote in message
> news:8AA29AA0-55D7-45E8-9F29-A19DDDA13F10@microsoft.com...
> > 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" <PercivalMound@discussions.microsoft.com> wrote in
> >> message
> >> news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
you're welcome
"PercivalMound" <PercivalMound@discussions.microsoft.com> wrote in message
news:55C44C14-F186-44FE-BD73-24906752D406@microsoft.com...
> 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" <PercivalMound@discussions.microsoft.com> wrote in
>> message
>> news:8AA29AA0-55D7-45E8-9F29-A19DDDA13F10@microsoft.com...
>> > 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" <PercivalMound@discussions.microsoft.com> wrote in
>> >> message
>> >> news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
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" <PercivalMound@discussions.microsoft.com> wrote in message
> news:8AA29AA0-55D7-45E8-9F29-A19DDDA13F10@microsoft.com...
> > 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" <PercivalMound@discussions.microsoft.com> wrote in
> >> message
> >> news:1F07376D-6A9E-4175-8819-86480FB0EBB2@microsoft.com...
> >> > 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?
> >>
> >>
> >>
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
"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? |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Notify me of replies