Sort Rows in order when totals are equal.
Posted by Alex Thorburn on August 27, 2001 9:03 AM
I have 7 rows of numbers x number of columns and a total column for each row. At present the total for each row is equal and I need to have the rows placed in decending order depending on the greater numbers of 6's 5's 4's etc in each row. Each row has numbers between 6 (the highest)& 1 (the lowest) Hopefully I have explained my problem, can anyone help with a formula to sort the rows in correct order
Posted by Eric on August 27, 2001 9:42 AM
I'm confused, could you post an example of the data as well as how it should look when the sort is done (NT)?
Posted by Alex Thorburn on August 27, 2001 11:51 AM
Tried pasting an example but it did'nt look right in this panel.
Maybe what I'm trying to say is that if row 1 has two 6's and a 3 it is better than two 6's and a 1 and 2.
Likewise one 6 a 5 and 4 is to be better than a 6,5 3 & 1 or a 6 3,3,3
The greater the number of higher numbers in any row that row to be top of any equal row with lesser numbers.
Blimey does that make sense
Posted by Eric on August 27, 2001 12:49 PM
Try this concatenated countif scheme, works fine until you get double digit counts
this works fine until you get double digit counts,
then you might want to paste the output as values and do a "text to columns" command.
=COUNTIF(A1:I1,6)&","&COUNTIF(A1:I1,5)&","&COUNTIF(A1:I1,4)&","&COUNTIF(A1:I1,3)&","&COUNTIF(A1:I1,2)&","&COUNTIF(A1:I1,1)
assuming your data starts in a1 and goes to a6
Posted by IML on August 27, 2001 3:55 PM
This is far from automatic, but lets say your numbers are in columns A through G. Put this formula in J1
=SMALL($A1:$G1,8-COLUMN(A1))
Now copy it over six more columns and down as far as you have data.
Now copy it, paste special value whereever you like.
Add your sum column at the end. Sort first by sum and then by your first column.
Not very pretty, but hopefully it will help.
Good luck.
Posted by Alex Thorburn on August 29, 2001 2:36 AM
Thanks but did not work for what I had in mind. I've resubmmited the query above under Sandy
