Ranking most frequent, problem with ties

[cassio]

Hi, I'm breaking my head over this, I wonder if any of you could help me out.

I work in a small library and I'm trying to rank our most frequent users. We already have a table with every borrowing of a book, with the name of the user in a column. How many times the user appears is equal to how many books he took.

I was able to count how many times they appear, but since there is both repeated numbers for the same user and also sometimes the users also tie between themselves, I couldn't solve the problem of ranking them properly. I want every user to appear, even if they tie. Here is how the table looks like, simplified, and also what I want to do:

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]User[/TD]
[TD]Books taken[/TD]
[TD] [/TD]
[TD]Ranking I want[/TD]
[TD]User[/TD]
[TD]Books taken[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]John[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Frank[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Mary[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Karen[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Bruno[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bruno[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'd appreciate any tip at this point. Thank you!

PS: I'm using Excel 2010

 

[Eric W]

Welcome to the forum.

Interesting problem. Fairly easy to define, fairly difficult to solve. Here's one option:

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]User[/TD]
[TD="bgcolor: #FAFAFA"]Books taken[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Ranking I want[/TD]
[TD="bgcolor: #FAFAFA"]User[/TD]
[TD="bgcolor: #FAFAFA"]Books taken[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA"]Frank[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]Frank[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA"]Mary[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]John[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]Karen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA"]Mary[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="bgcolor: #FAFAFA"]Bruno[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA"]Karen[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA"]Frank[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FAFAFA"]Mary[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #FAFAFA"]Bruno[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(F4="","",COUNTIF($F$3:$F3,">"&F4)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]{=IF(F3="","",INDEX($A$3:$A$20,MIN(IF((SUMIF($A$3:$A$20,$A$3:$A$20,$B$3:$B$20)=F3)*(COUNTIF(E$2:E2,$A$3:$A$20)=0),ROW($A$3:$A$20)-ROW($A$3)+1))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]{=IFERROR(LARGE(IF($A$3:$A$20<>"",IF(MATCH($A$3:$A$20,$A$3:$A$20,0)=ROW($A$3:$A$20)-ROW($A$3)+1,SUMIF($A$3:$A$20,$A$3:$A$20,$B$3:$B$20))),ROWS(F$3:F3)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]




Put the F3 formula in. Change the ranges to match your sheet. You can use a large range with empty rows on the end to allow for growth. Confirm with Control+Shift+Enter, and drag down. Repeat with the E2 formula. In D3, put a 1. Then put the non-array formula in E4 and drag down.

Let me know if this works for you.