repost: connect two rows of data
jeffP
Here is my dilemma. A is name b:E data, F is sum of each row and every other
row I add the sums is F, so each name has a 2subtotal rows and one grand
total
I need to sort by the grand total but keep the two rows of data together.
Any tricks or tips ? All help is appreciated.
Smith 5 4 5 3 17
6 5 5 4 20 37
Jones 4 3 3 3 13
5 5 5 5 20 33
white 5 4 4 4 17
4 3 3 3 13 30
Pete 5 5 5 5 20
6 5 4 3 18 38
--
jeffP
jppo...@tooHotmail.com
arno
> 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
I was a little confused but....this worked perfectly.
Thanks for your help.
--
jeffP
jppo...@tooHotmail.com
"arno" <schob...@azoppoth.at> wrote in message
news:OZbGLoyY...@TK2MSFTNGP12.phx.gbl...
David McRitchie
Nice solution and a non programming one. Was surprised that the
formulas did not need to be converted to constants before sorting.
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
arno
> 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