Sort Columns of data at the same time, without regard to order.
 

I have a spreadsheet where I keep track of student's marks for tests. At the
end of the term, I would like to sort the marks, without keeping them lined
up with the test number, so I can keep the top 5 marks for each student.

Example:

Student/test s1 s2 s3 s4 s5
test 1 7 8 0 2 10
test 2 10 5 10 8 9
test 3 9 8 9 10 5
test 4 2 5 10 8 8

In the above example I would want to sort each column so that they were
highest to lowest, without affecting the column with test 1 etc. I just want
to sort the marks to see the top so many, and grade based on that.

I used to block of the values only, and hit sort, and all columns would be
sorted, but that doesn't work in Excel 2007.

Any help would be appreciated.

Sort Columns of data at the same time, without regard to order.
 

<I used to block of the values only, and hit sort, and all columns would be
sorted

How could you do this in Excel 2003 - please let us know. As far as I know,
if there are multiple columns, you have to specify a sort order I.e. first
by this, then by this and so on. How could you sort all columns
independently in one go

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Herc" wrote in message
...
I have a spreadsheet where I keep track of student's marks for tests. At
the
end of the term, I would like to sort the marks, without keeping them
lined
up with the test number, so I can keep the top 5 marks for each student.

Example:

Student/test s1 s2 s3 s4 s5
test 1 7 8 0 2 10
test 2 10 5 10 8 9
test 3 9 8 9 10 5
test 4 2 5 10 8 8

In the above example I would want to sort each column so that they were
highest to lowest, without affecting the column with test 1 etc. I just
want
to sort the marks to see the top so many, and grade based on that.

I used to block of the values only, and hit sort, and all columns would be
sorted, but that doesn't work in Excel 2007.

Any help would be appreciated.

Sort Columns of data at the same time, without regard to order
 

Ashish,

Thank you for all your help. I was requesting on behalf of another
instructor, and since they only wanted to find out the top 5 quiz marks, I
thought I would check out your website and some of your solutions there. I
found a formula (LARGE) which will do exactly what the instructors want and
make everyones life here easier.

Thanks again. (I don't know how they were able to sort the whole block, I
was going by what they told me, I think it was in a much older version of
Excel.)

"Ashish Mathur" wrote:

<I used to block of the values only, and hit sort, and all columns would be
sorted

How could you do this in Excel 2003 - please let us know. As far as I know,
if there are multiple columns, you have to specify a sort order I.e. first
by this, then by this and so on. How could you sort all columns
independently in one go

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Herc" wrote in message
...
I have a spreadsheet where I keep track of student's marks for tests. At
the
end of the term, I would like to sort the marks, without keeping them
lined
up with the test number, so I can keep the top 5 marks for each student.

Example:

Student/test s1 s2 s3 s4 s5
test 1 7 8 0 2 10
test 2 10 5 10 8 9
test 3 9 8 9 10 5
test 4 2 5 10 8 8

In the above example I would want to sort each column so that they were
highest to lowest, without affecting the column with test 1 etc. I just
want
to sort the marks to see the top so many, and grade based on that.

I used to block of the values only, and hit sort, and all columns would be
sorted, but that doesn't work in Excel 2007.

Any help would be appreciated.

Sort Columns of data at the same time, without regard to order
 

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Herc" wrote in message
...
Ashish,

Thank you for all your help. I was requesting on behalf of another
instructor, and since they only wanted to find out the top 5 quiz marks, I
thought I would check out your website and some of your solutions there.
I
found a formula (LARGE) which will do exactly what the instructors want
and
make everyones life here easier.

Thanks again. (I don't know how they were able to sort the whole block, I
was going by what they told me, I think it was in a much older version of
Excel.)

"Ashish Mathur" wrote:

<I used to block of the values only, and hit sort, and all columns would
be
sorted

How could you do this in Excel 2003 - please let us know. As far as I
know,
if there are multiple columns, you have to specify a sort order I.e.
first
by this, then by this and so on. How could you sort all columns
independently in one go

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Herc" wrote in message
...
I have a spreadsheet where I keep track of student's marks for tests.
At
the
end of the term, I would like to sort the marks, without keeping them
lined
up with the test number, so I can keep the top 5 marks for each
student.

Example:

Student/test s1 s2 s3 s4 s5
test 1 7 8 0 2 10
test 2 10 5 10 8 9
test 3 9 8 9 10 5
test 4 2 5 10 8 8

In the above example I would want to sort each column so that they were
highest to lowest, without affecting the column with test 1 etc. I
just
want
to sort the marks to see the top so many, and grade based on that.

I used to block of the values only, and hit sort, and all columns would
be
sorted, but that doesn't work in Excel 2007.

Any help would be appreciated.