Top 3 Items

[Sallyz98]

First time poster, long time reader

Platform: Excel 2010

My report resembles the attached image

The list is about 40 deep. The data, pulled from a program, is pasted into sheet 1 and everything is countifs into the appropriate cell on sheet 2 for the pretty report you see above.

My new ask to add to my report is a new section that would say China top 3 issues: (300) - 23, (400) 17, (3100) - 3 etc. The another for Japan and other countries. Next month, when the new data is pasted, it would update the top 3 issues per country. The allegation types are all unique and there may be a tie for any of the places.

I'm not comfortable enough with VBA.

Any ideas?

 

[shg]

You could autofilter to see the top 3 issue for China, hide the Japan columns, and save that as a custom view. Then repeat for Japan.

 

[Sallyz98]

You could autofilter to see the top 3 issue for China, hide the Japan columns, and save that as a custom view. Then repeat for Japan.

How would I do that? I also don't want to edit that table, I want the results to show up below the table.

 

[shg]

Create a view with everything exposed: View > Custom Views > Add, call it Normal (or whatever tickles your fancy).

Hide the Japan columns, filter what's left, and create a view called China.

Go back to view Normal, hide the China columns, filter what's left, and create a view called Japan.

 

[Sallyz98]

I'm failing miserably. I want the original table to stay as is and this to be another thing under it.

 

[shg]

The original table would be back where it was if you just select the view named Normal, Sally.

 

[Sallyz98]

Maybe I'm just totally missing it here sorry

But in the end, this is a report that I'm printing (as PDF) and handing out to multiple people. I need the top 3 to show up as well as the original table to stay as is.

My Googling (before posting here), showed the answer using index. I've never used index before and was hoping for an easier solution

 

[Daveydoodles]

You could use the LARGE( function.

So say the values are in A1:A10 - LARGE(A1:A10,1) will return the highest value, 2 for the second and 3 for the third etc...

If you want the Allegation reference in an adjacent cell, use a lookup like INDEX and MATCH;

e.g. INDEX(B1:B10,MATCH(A11,A1:A10,0)) - where B1:B10 contains allegation references and A11 contains the above formula returning the value.

 

[StefanVL]

[TABLE="width: 640"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]H[/TD]
[TD="width: 64"]I[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Top[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]N[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
In I3:
=COUNTIF($F$2:$F$8;">="&LARGE($F$2:$F$8;$I$2))

In I5:
=IF(ROWS($I$5:I5)<=$I$3;LARGE($F$2:$F$8;ROWS($I$5:I5));"")

In H5:
=IF($I5="";"";INDEX($A$2:$A$8;SMALL(IF($F$2:$F$8=$I5;ROW($F$2:$F$8)-ROW($F$2)+1);COUNTIF($I$5:I5;$I5))))
Enter with Control + Shift + Enter

This method will allow a shared number 3 spot.