Generate Top 3 Values and Associated Names with Duplicates

[manona]

Hi,

I would like to generate a list with the top 3 people with the highest number of incidents in one year (including their name and count).

I have a list of names with their incident counts, but I have trouble listing the top 3 because there are duplicates in the incident counts.

I can't do pivot table or anything manual as this feeds into a chart/dashboard automatically.

Any thoughts are appreciated!

Manon

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A (name)[/TD]
[TD]B (#incidents)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Peter[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Alex[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Denise[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sylvie[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Lucie[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Daniel[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Michel[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]TOP 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Name[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Why not simply sort the data by Incident and use the first 3 lines in you top 3 section.
you will only ever get the duplicates to appear alphabetically unless there are other criteria

Data sorted

Excel 2007
	A	B	C
1	A (name)	B (#incidents)
2	Sylvie	4
3	Denise	3
4	Alex	2
5	Lucie	2
6	Michel	2
7	Peter	1
8	Daniel	1
9
10
11	TOP 3
12	Name	Count
13	Sylvie	4
14	Denise	3
15	Alex	2
16
Sheet1

Cell Formulas
Range	Formula
A13	=A2
A14	=A3
A15	=A4
B13	=B2
B14	=B3
B15	=B4

 

[manona]

Hi Michael,

Thank you for your input!

Note also that the list of people and their incident count is also formula driven (to extract unique (name) values from the database and to count their incidents).

Therefore I can do the sort manually now, but I don't think it would automatically update with new entries in the database. I also have 2 'tables' next to one another (one for students and one for teachers), so the sort wouldn't work in this particular case.

Thanks anyways Michael!

Manon

 

[Michael M]

Ok, another option.....BUT in the result of ties, the answer will ALWAYS be the first one found

Excel 2007
	A	B
4	A (name)	B (#incidents)
5	Peter	1
6	Alex	2
7	Denise	3
8	Sylvie	4
9	Lucie	2
10	Daniel	1
11	Michel	2
12
13	TOP 3
14	Name	Count
15	Sylvie	4
16	Denise	3
17	Alex	2
Sheet1

Cell Formulas
Range	Formula
A15	=INDEX($A$5:$A$11,MATCH(LARGE($B$5:$B$11,1),$B$5:$B$11,0))
A16	=INDEX($A$5:$A$11,MATCH(LARGE($B$5:$B$11,2),$B$5:$B$11,0))
A17	=INDEX($A$5:$A$11,MATCH(LARGE($B$5:$B$11,3),$B$5:$B$11,0))
B15	=VLOOKUP(A15,$A$5:$B$11,2,0)
B16	=VLOOKUP(A16,$A$5:$B$11,2,0)
B17	=VLOOKUP(A17,$A$5:$B$11,2,0)

 

[manona]

Thank you Michael! Would this still work if let's say 'Alex' also has 3 accidents?

 

[Michael M]

Did you try it ??

 

[manona]

Yes, I get the same result twice.

 

[Michael M]

Ah, sorry...I was missing you point..

You will need a helper column, like this

Excel 2007
	A	B	C
4	A (name)	B (#incidents)	Ranking
5	peter	1	6
6	Alex	3	2
7	Denise	3	3
8	Sylvie	4	1
9	Lucie	2	4
10	Daniel	1	7
11	Michel	2	5
12
13	TOP 3
14	Name	Count
15	Sylvie	4
16	Alex	3
17	Denise	3
18
Sheet1

Cell Formulas
Range	Formula
C5	=RANK(B5,$B$5:$B$11)+COUNTIF(B$5:B5,B5)-1
C6	=RANK(B6,$B$5:$B$11)+COUNTIF(B$5:B6,B6)-1
C7	=RANK(B7,$B$5:$B$11)+COUNTIF(B$5:B7,B7)-1
C8	=RANK(B8,$B$5:$B$11)+COUNTIF(B$5:B8,B8)-1
C9	=RANK(B9,$B$5:$B$11)+COUNTIF(B$5:B9,B9)-1
C10	=RANK(B10,$B$5:$B$11)+COUNTIF(B$5:B10,B10)-1
C11	=RANK(B11,$B$5:$B$11)+COUNTIF(B$5:B11,B11)-1
A15	=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A2),$C$5:$C$11,0))
A16	=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A3),$C$5:$C$11,0))
A17	=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A4),$C$5:$C$11,0))
B15	=VLOOKUP(A15,$A$5:$B$11,2,0)
B16	=VLOOKUP(A16,$A$5:$B$11,2,0)
B17	=VLOOKUP(A17,$A$5:$B$11,2,0)

 

[Aladin Akyurek]

Book1
	A	B
1	A (name)	B (#incidents)
2	Peter	1
3	Alex	2
4	Denise	3
5	Sylvie	4
6	Lucie	2
7	Daniel	1
8	Michel	2
9
10	Top	3
11	Adjusted	5
12	Top Names	Top Scores
13	Sylvie	4
14	Denise	3
15	Alex	2
16	Lucie	2
17	Michel	2
Sheet1

In B11 just enter:

=COUNTIFS(B2:B9,">="&LARGE(B2:B9,MIN(B10,COUNT(B2:B8))))

In A13 control+shift+enter, not just enter, and copy down:

=IF($B13="","",INDEX($A$2:$A$9,SMALL(IF($B$2:$B$9=B13,ROW($B$2:$B$9)-ROW($B$2)+1),COUNTIFS($B$13:B13,B13))))

In B13 just enter and copy down:

=IF(ROWS($B$13:B13)<=$B$11,LARGE($B$2:$B$9,ROWS($B$13:B13)),"")

 

[manona]

Thank you, it worked perfectly!!