dynamically sorted list with duplicates

[SeanGerman]

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))),"",LARGE(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))),"",LARGE(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))),"",LARGE(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

 

[Ashish Mathur]

Hi,

May I request you to mail me the workbook and explain the problem very
clearly.

--
Regards,

Ashish Mathur
www.ashishmathur.com
http://www.linkedin.com/in/excelenthusiasts

 

[Max]

One alternative formulas set-up
to retrieve it dynamically in sorted descending order by rank in Sheet3

Illustrated in this sample:
http://www.freefilehosting.net/download/3g9md
Full dynamic sorted list from 2 shts w ties.xls

In Sheet3
In A1: =IF(Sheet1!B2="","",ROW())
In B1: =IF(Sheet2!B2="","",ROW())
In C1:
=IF(ROW()>COUNT($A:$A),IF(ROW()-MAX($A:$A)>COUNT($B:$B),"",INDEX(Sheet2!A:A,SMALL($B:$B,ROW()-MAX($A:$A))+1)),INDEX(Sheet1!A:A,SMALL($A:$A,ROW())+1))
Copy C1 to E1
In F1: =IF(D1="","",D1-ROW()/10^10)
Select A1:F1, copy down to cover the max expected extents of the combined
data in both Sheet1 and Sheet2, say down to F20

Then place
In G1: =IF(H2="","",ROWS($1:1))
In H1:
=IF(ROWS($1:1)>COUNT($F:$F),"",INDEX(C:C,MATCH(LARGE($F:$F,ROWS($1:1)),$F:$F,0)))
Copy H1 to J1. Select G1:J1, fill down to J21 (one row beyond the extent
filled in cols A to F). Hide away cols A to F. Cols H to J will return the
desired full combined results (inclusive of lines with tied ranks), sorted
in descending order by the ranks. Col G provides the auto-numbering for the
results set.

 

[Max]

You'd get the required sorted list returned in Sheet3 below:

Name Rank Sex
Eric 6 Boy
Betty 5 Girl
Bruce 5 Boy
Alice 3 Girl
Cathy 3 Girl
Adam 3 Boy
David 2 Boy
Donna 1 Girl
Frank 1 Boy
Carl 0 Boy

 

[SeanGerman]

One alternative formulas set-up
to retrieve it dynamically in sorted descending order by rank in Sheet3

Illustrated in this sample:http://www.freefilehosting.net/download/3g9md
Full dynamic sorted list from 2 shts w ties.xls

In Sheet3
In A1: =IF(Sheet1!B2="","",ROW())
In B1: =IF(Sheet2!B2="","",ROW())
In C1:
=IF(ROW()>COUNT($A:$A),IF(ROW()-MAX($A:$A)>COUNT($B:$B),"",INDEX(Sheet2!A:A­,SMALL($B:$B,ROW()-MAX($A:$A))+1)),INDEX(Sheet1!A:A,SMALL($A:$A,ROW())+1))
Copy C1 to E1
In F1: =IF(D1="","",D1-ROW()/10^10)
Select A1:F1, copy down to cover the max expected extents of the combined
data in both Sheet1 and Sheet2, say down to F20

Max,

Thank you very much. Clever solution to the issue of repeated
rankings. And I did not know a range of cells could be specified by
column--$A:A$ as opposed to $A1:$A20.

All very useful. Terrific.

Ashish Mathur, Thank you for the offer, but I'm all set thanks to Max.


Thanks,



Sean

 

[Max]

Welcome, Sean.

Max,

Thank you very much. Clever solution to the issue of repeated
rankings. And I did not know a range of cells could be specified by
column--$A:A$ as opposed to $A1:$A20.

All very useful. Terrific.

Ashish Mathur, Thank you for the offer, but I'm all set thanks to Max.

Thanks,
Sean