--
jeffP
jppo...@tooHotmail.com
> I need to sort by the grand total but keep the two rows of data together.
add two columns (here x and y). in x1 you write
=a1&1
(where a1 shows "smith")
in x2 you write
=a1&2
in y1 you write:
=y2
in y2 you write:
=g2
where g2 shows the total of 37.
the result for x and y would be:
smith1 37
smith2 37
then you copy range (x1:y2) and paste it down the list, then you can sort
first by column y and then by x.
this will *only*work*if* ***all*** your records are using 2 lines.
arno
--
jeffP
jppo...@tooHotmail.com
"arno" <schob...@azoppoth.at> wrote in message
news:OZbGLoyY...@TK2MSFTNGP12.phx.gbl...
Excel sorts retain the order, so you actually only need the extra column
that carries the Grand Total (G) for sorting. Carrying both extra columns
though provides the ability to sort on names instead. You actually would
be better off leaving out the 1 & 2 suffixes on the name for the same
reason that Excel does retain the order of items not in the sort keys.
I've added your solution to my Fill Handle page
Sorting on a Value That Appears in Alternate Rows (#sortbyalt)
http://www.mvps.org/dmcritchie/excel/fillhand.htm#sortbyalt
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"arno" <schob...@azoppoth.at> wrote...
> hi jeff,
> in y1 you write: =G2 (corrected)
> in y2 you write: =G2
> I've added your solution to my Fill Handle page
> Sorting on a Value That Appears in Alternate Rows (#sortbyalt)
> http://www.mvps.org/dmcritchie/excel/fillhand.htm#sortbyalt
:))
> Excel sorts retain the order, so you actually only need the extra column
> that carries the Grand Total (G) for sorting.
no,no,no,no! my solution will _always_ work, it does not depend on the
software. (what about making a query with access to the excel table?
wouldn't it be nice to be _absolutely_ sure about the sort order? what does
the excel sorting feature then help - nothing!) Whenever I reply to
somebody with a question re. sorting, I always stress the point that you
first try to get the data in the correct format from database-queries etc.,
if this is not possible make (dummy) columns that are representing the sort
criteria, then (with some textfunctions) you can concatenate the criteria to
another column (if you have more than 3 sorting columns) and then you sort.
So, you wanna sort a table? Then make sure you have _all_ the data that's
neccessary!
regards
arno