Top n values within multiple categories - thread started in 2006!!

[Rach J]

Hello,

I found this very old thread and recreated the example to work out how to apply it to my data. I have posted it as a new topic as it is a very old thread and I wasn't sure if it would be seen - I hope this is ok?!!

Whilst this solution correctly identifies the top n scores, it will then go on to list the first people in the list with the corresponding scores regardless of what category they are in... in the below example from Aladin (which I found very easy to follow - thank you! ); Dawn, Stevie & Matt all have 75 points and are in category x, so they are correctly listed in the top scoring table, however, if you change Dawn's category to y, the number of top scores correctly changes to 4, but the corresponding names do not appear correctly - Dawn and Stevie appear in the list, not Stevie and Matt as it should be.... does this make sense?

Can anyone help me to alter this formula to correctly rank the top n values within a category?
=IF(N(E5),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E5,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E5,E5))),"")

I would attach my recreated version of the example... but I haven't worked out how to do that yet!!!

Many thanks,
Rach

******** ******************** ************************************************************************><center>[TABLE="align: center"]
<tbody>[TR]
[TD="colspan: 7"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD="align: left"]Microsoft Excel - Conditional Top 10 List LilStevie.xls[/TD]
[TD="align: right"]___Running: 11.0 : OS = Windows XP[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: #d4d0c8, colspan: 7"][TABLE="width: 100%, align: center"]
<tbody>[TR]
[TD](F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout[/TD]
[TD="align: center"]<form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" type="button" value="Copy Formula" name="btCb942116"></form>[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD="bgcolor: white, colspan: 7"]

<tbody>
[TD="bgcolor: white"]<select name="sltNb447362"><option value="{=SUM(IF(C5:C14=E1,IF(B5:B14>=LARGE(IF(C5:C14=E1,B5:B14,""),E2),1,0)))}" selected="">E3</option><option value="{=IF(ROWS(E$5:E5)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E5)),"")}">E5</option><option value="{=IF(N(E5),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E5,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E5,E5))),"")}">F5</option><option value="{=IF(ROWS(E$5:E6)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E6)),"")}">E6</option><option value="{=IF(N(E6),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E6,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E6,E6))),"")}">F6</option><option value="{=IF(ROWS(E$5:E7)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E7)),"")}">E7</option><option value="{=IF(N(E7),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E7,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E7,E7))),"")}">F7</option><option value="{=IF(ROWS(E$5:E8)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E8)),"")}">E8</option><option value="{=IF(N(E8),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E8,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E8,E8))),"")}">F8</option><option value="{=IF(ROWS(E$5:E9)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E9)),"")}">E9</option><option value="{=IF(N(E9),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E9,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E9,E9))),"")}">F9</option><option value="{=IF(N(E10),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E10,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E10,E10))),"")}">F10</option></select>[/TD]
[TD="bgcolor: #d4d0c8, align: right"] = [/TD]
[TD="bgcolor: white, align: left"]<input size="80" value="{=SUM(IF(C5:C14=E1,IF(B5:B14>=LARGE(IF(C5:C14=E1,B5:B14,""),E2),1,0)))}" name="txbFb150492">[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="bgcolor: #D4D0C8"]<center>A</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>B</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>C</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>D</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>E</center>[/TD]
[TD="bgcolor: #D4D0C8"]<center>F</center>[/TD]
[/TR]
[TR]
[TD]<center>1</center>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]x[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>2</center>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>3</center>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>4</center>[/TD]
[TD]Name[/TD]
[TD="align: right"]Score[/TD]
[TD]Text[/TD]
[TD="align: right"][/TD]
[TD]Top Score(s)[/TD]
[TD]Top Performer(s)[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>5</center>[/TD]
[TD]dawn[/TD]
[TD="align: right"]75[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: center"]90[/TD]
[TD]brian[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>6</center>[/TD]
[TD]damon[/TD]
[TD="align: right"]85[/TD]
[TD]y[/TD]
[TD="align: right"][/TD]
[TD="align: center"]90[/TD]
[TD]jon[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>7</center>[/TD]
[TD]dan[/TD]
[TD="align: right"]70[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: center"]75[/TD]
[TD]dawn[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>8</center>[/TD]
[TD]brian[/TD]
[TD="align: right"]90[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: center"]75[/TD]
[TD]stevie[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>9</center>[/TD]
[TD]christine[/TD]
[TD="align: right"]85[/TD]
[TD]y[/TD]
[TD="align: right"][/TD]
[TD="align: center"]75[/TD]
[TD]matt[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>10</center>[/TD]
[TD]ian[/TD]
[TD="align: right"]80[/TD]
[TD]y[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>11</center>[/TD]
[TD]jon[/TD]
[TD="align: right"]90[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>12</center>[/TD]
[TD]stevie[/TD]
[TD="align: right"]75[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>13</center>[/TD]
[TD]fran[/TD]
[TD="align: right"]60[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>14</center>[/TD]
[TD]matt[/TD]
[TD="align: right"]75[/TD]
[TD]x[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="width: 2%, bgcolor: #D4D0C8"]<center>15</center>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D4D0C8, colspan: 7"][TABLE="width: 100%, align: left"]
<tbody>[TR]
[TD="bgcolor: #FFFFFF, align: left"]Data[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

E1: x

which houses the string condition.

E2: 3

which houses the desired size of the Top N list.

E3:

=SUM(IF(C5:C14=E1,IF(B5:B14>=LARGE(IF(C5:C14=E1,B5:B14,""),E2),1,0)))

which must be confirmed with control+shift+enter (not just with enter).

This establishes the factual size of the Top N list.

E5:

=IF(ROWS(E$5:E5)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E5)),"")

which must be confirmed with control+shift+enter (not just with enter) the copied down.

This determines the actual Top N scores.

F5:

=IF(N(E5),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E5,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E5,E5))),"")

which must be confirmed with control+shift+enter (not just with enter) the copied down.

This determines the actual names associated wit the Top N scores.

 

[martindwilson]

maybe something like this

Excel Workbook
	A	B	C	D	E	F	G	H	I
1					x
2					6
3					8
4	Name	Score	Text		Top Score(s)	Top Performer(s)
5	dawn	75	x		90	brian		75	6
6	damon	85	y		90	jon
7	dan	70	x		75	dawn		70	3
8	brian	90	x		75	stevie		90	8
9	christine	85	y		75	matt
10	ian	80	y		70	dan
11	jon	90	x					90	7
12	stevie	75	x					75	5
13	fran	60	x					60	2
14	matt	75	x					75	4
15	harry	20	x					20	1
16	mike	10	y
Sheet1

Excel Workbook
	H	I
5	75	6
Sheet1

Excel Workbook
	E
3	8
Sheet1

 

[Rach J]

Thanks martindwilson! This does the trick!

It takes quite a while to calculate though (5000 lines of data), so if you have any tips for speeding it up I would be very grateful!

Thanks for replying quickly with a solution - much appreciated!

Rach