CODEWITHSUNDEEP
excelduplicatesmax
user7733554
user7733554

Reputation: 11

Excel - Finding nth largest value with duplicate data

I have a the following table, it has more columns and is 40 rows long but this is an example of the data. The table is sorted by Team #

Data Table

enter image description here

I am trying to create a 2nd table that shows the top 10 teams that delivered gears. I want to do this for the other columns as well. I am trying to do this without VBA.
I used this function and it worked well:

=INDEX(TT_Team,MATCH(LARGE(TT_Tele_Gears,$A3),TT_Tele_Gears,0))

The problem is the duplicate data for the amount of gears delivered IF two teams have delivered the same number of gears I want to show them both, but do not care about which is #1 or #2 Currently I get this:

Top 10 Table

enter image description here

Any ideas on a fix ?

Thanks in Advance

Upvotes: 0

Views: 2270

Answers (1)

barry houdini
barry houdini

Reputation: 46331

You could try a solution like this:

enter image description here

The formula in F2 copied down is just:

=LARGE(B$2:B$12,D2)

and in E2 as shown it's this:

=INDEX(A$2:A$12,LARGE(IF(B$2:B$12=F2,ROW(B$2:B$12)-ROW(B$2)+1),COUNTIF(F2:F$6,F2)))

confirm with CTRL+SHIFT+ENTER and copy down

It's the COUNTIF part at the end that makes the difference. This is counting from the current row, so for duplicates as you go down the column the COUNTIF value changes, so you get each duplicate

Upvotes: 2

Related Questions