Howdy,

I need to create a sorted list from tables of values--not too
exciting--but I'm having trouble handling arbitrary number of
duplicates. Working with Excel 2003.

In a single workbook, I have sheet 1:
Name Rank Sex
Alice 3 Girl
Betty 5 Girl
Cathy 3 Girl
Donna 1 Girl


And sheet 2:
Name Rank Sex
Adam 3 Boy
Bruce 5 Boy
Carl 0 Boy
David 2 Boy
Eric 6 Boy
Frank 1 Boy


On sheet 3 I want, by rank highest to lowest:
Eric 6 Boy
Betty 5 Girl
Buce 5 Boy
Alice 3 Girl
Cathy 3 Girl
Adam 3 Boy
David 2 Boy
Donna 1 Girl
Frank 1 Boy
Carl 0 Boy

The order of names of the same rank is not an issue (Betty and Bruce
vs Bruce and Betty) but I do need all the names. I know all the names
will be unique, but I don't know how many duplicates in rank there
will be.

What I have right now is:
Eric 6 Boy
Betty 5 Girl
Betty 5 Girl
Alice 3 Girl
Alice 3 Girl
Alice 3 Girl
David 2 Boy
Donna 1 Girl
Donna 1 Girl
Carl 0 Boy

What I'm doing is first create a single table on sheet 3:
Column A: =IF(B1="","",INDEX(Sheet1!A$1:A$10,MATCH(B1,Sheet1 !B$1:B
$10,0)))
Column B: =IF(ISERROR(LARGE(Sheet1!B$1:B$10,ROW(A1))),"",LAR GE(Sheet1!B
$1:B$10,ROW(A1)))
Column C: =IF(B1="","",INDEX(Sheet1!C$1:C$10,MATCH(B1,Sheet1 !B$1:B
$10,0)))

for all the rows with data on sheet 1, and then rows for all the data
on sheet 2, with "sheet2" in place of "sheet1" in the formulas. A1
and B1 for the first row. Values are A2 and B2 for the second row,
and so on.

Then I sort the consollidated list:
Column G :=IF(H1="","",INDEX(A$1:A$20,MATCH(H1,B$1:B$20,0)) )
Column H: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B
$1:B$20,ROW(A1)))
Column I: =IF(H1="","",INDEX(C$1:C$20,MATCH(H1,B$1:B$20,0)))

I played around with having the formula in column A check if a cell
has the same value as the cell above, but that doesn't really address
the issue if there is more than 2 names with the same rank.

I tried making a seperate table with ranks and multiplicities:
Column M: =IF(ISERROR(LARGE(Sheet3!B$1:B$20,ROW(A1))),"",LAR GE(Sheet3!B
$1:B$20,ROW(A1)))
Column N(first row): 1
Column N(rest): =IF(AND(M2=M1,M2<""),N1+1,1)

which gives me this:
6 1
5 1
5 2
3 1
3 2
3 3
2 1
1 1
1 2
0 1

So I know for the third entry, this is the second occurance of rank 5.

How do I find the index of that second occurance? Is there a way to
tell the MATCH and INDEX formulas for column I to search from the
first occurance to B$20 and C$20, rather than always from B$1:B$20?

Thanks,


Sean