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]

 

[sandy666]

or with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A (name)[/td][td=bgcolor:#5B9BD5]B (#incidents)[/td][td][/td][td=bgcolor:#70AD47]B (#incidents)[/td][td=bgcolor:#70AD47]A (name)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Peter[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]

4​

[/td][td=bgcolor:#E2EFDA]Sylvie[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Alex[/td][td]

2​

[/td][td][/td][td]

3​

[/td][td]Denise[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Denise[/td][td=bgcolor:#DDEBF7]

3​

[/td][td][/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA]Lucie, Michel, Alex[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Sylvie[/td][td]

4​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Lucie[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Daniel[/td][td]

1​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Michel[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A (name)", type text}, {"B (#incidents)", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"B (#incidents)", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"B (#incidents)"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "A (name)", each Table.Column([Count],"A (name)")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"A (name)", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"Count"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",3)
in
    #"Kept First Rows"[/SIZE]

 

[Aladin Akyurek]

Thank you, it worked perfectly!!

You are welcome. Thanks for the update.

 

[manona]

Thank you Michael, it worked perfectly!

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

You will need a helper column, like this
Excel 2007

	A	B	C
A (name)	B (#incidents)	Ranking
peter
Alex
Denise
Sylvie
Lucie
Daniel
Michel
TOP 3
Name	Count
Sylvie
Alex
Denise

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C5[/TH]
[TD="align: left"]=RANK(B5,$B$5:$B$11)+COUNTIF(B$5:B5,B5)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C6[/TH]
[TD="align: left"]=RANK(B6,$B$5:$B$11)+COUNTIF(B$5:B6,B6)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C7[/TH]
[TD="align: left"]=RANK(B7,$B$5:$B$11)+COUNTIF(B$5:B7,B7)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C8[/TH]
[TD="align: left"]=RANK(B8,$B$5:$B$11)+COUNTIF(B$5:B8,B8)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C9[/TH]
[TD="align: left"]=RANK(B9,$B$5:$B$11)+COUNTIF(B$5:B9,B9)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C10[/TH]
[TD="align: left"]=RANK(B10,$B$5:$B$11)+COUNTIF(B$5:B10,B10)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]C11[/TH]
[TD="align: left"]=RANK(B11,$B$5:$B$11)+COUNTIF(B$5:B11,B11)-1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A15[/TH]
[TD="align: left"]=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A2),$C$5:$C$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B15[/TH]
[TD="align: left"]=VLOOKUP(A15,$A$5:$B$11,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A16[/TH]
[TD="align: left"]=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A3),$C$5:$C$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B16[/TH]
[TD="align: left"]=VLOOKUP(A16,$A$5:$B$11,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]A17[/TH]
[TD="align: left"]=INDEX($A$5:$A$11,MATCH(ROWS($A$2:A4),$C$5:$C$11,0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]B17[/TH]
[TD="align: left"]=VLOOKUP(A17,$A$5:$B$11,2,0)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]