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:

User	Books taken		Ranking I want	User	Books taken
John	3		1	John	3
John	3		2	Frank	2
Frank	2		2	Mary	2
John	3		3	Karen	1
Mary	2		3	Bruno	1
Karen	1
Frank	2
Mary	2
Bruno	1

<tbody>
</tbody>

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
1	User	Books taken		Ranking I want	User	Books taken
2
3	John	3		1	John	9
4	John	3		2	Frank	4
5	Frank	2		2	Mary	4
6	John	3		4	Karen	1
7	Mary	2		4	Bruno	1
8	Karen	1
9	Frank	2
10	Mary	2
11	Bruno	1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula D4 =IF(F4="","",COUNTIF($F$3:$F3,">"&F4)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E3 {=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))))} F3 {=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)),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.