How to create a top 5 list without duplicate values

Marcus99

Hey there, I am trying to create a top 5 list of values from a large table of information. the idea is that you would be able to see which "ATA Chapters" have the highest figure next to them, for example if you look at the attached image "ATA 25 - Equipment & Furnishings" has a total of 5 in its row if you add the numbers up so this would be number 1 in the top 5.

This is not the issue, I am able to generate this list but the problem comes when there are multiple ATA chapters with the same totals for example the table on the right shows "ATA 27 - Flight Controls" as number 2, 3, 4 & 5. I only want this to show as number 2 and then for the formula to look at the next highest figure.

My formulas currently look like this

=LARGE([Total ]1:[Total ]45, 1) - to calculate the total figure in the top 5

=INDEX([ATA Chapter]1:[ATA Chapter]45, MATCH(LARGE([Total ]1:[Total ]45, 1), [Total ]1:[Total ]45, 0)) - to show the ATA chapter name in the top 5

Genevieve P.

Hey @Marcus99

What I would do here is count how many times the value in the "Top 5 Reports" column appears from the top row til the current row. This way we could return if it's the second time this number appears, or third, etc.

Then we can use that in an index COLLECT combination.

=INDEX(COLLECT(column to filter, criteria to filter), row number)

Normally we filter the Collect function so there's only 1 row to bring back, so we put a 1 at the end. In this case, since the criteria has multiple matches (e.g. the four different rows that all have the same total), we need the row number to be dynamic: 1 for the first match, 2 for the second, and so on. This is where a COUNTIF helps us out:

COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row)

It identifies if, based on your list in the Top 5 Reports column, this is the first duplicate, second, third, etc.

Try this as your INDEX(COLLECT:

=INDEX(COLLECT([ATA Chapter]:[ATA Chapter], Total:Total, [Top 5 Reports]@row), COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row))

Note that I removed the space after [Total ] in your formula - you may need to double check that the Total column name matches what's in the formula.

Let me know if that makes sense and works for you!

Cheers,

Genevieve

Marcus99

I think I'll need a little clarification on this one 😂

Genevieve P.

Hey @Marcus99

The INDEX(COLLECT formula above is to replace your current INDEX(MATCH formula. Keep your Top 5 column the exact same, but use INDEX(COLLECT in the other one instead!

=INDEX(COLLECT([ATA Chapter]:[ATA Chapter], Total:Total, [Top 5 Reports]@row), COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row))

***

The formula above should work without understanding why, but here's the explanation:

***

• The first range listed is the column to return based on other criteria - your ATA Chapter column.
• The second column listed is the column to filter by - the Total column
• The criteria you're looking for in the Total column is if it matches the number returned in the Top 5 Reports cell

Where you were getting stuck is that if the number is the same in the Total column (e.g your rank 2, 3, 4, and 5 are all a value of 3) then you're only seeing the first match returned, so even though there are four separate ATA Chapters that all have a value of 3, the INDEX(MATCH finds the first match each time.

With an INDEX(COLLECT, you tell it if you want the first match:

=INDEX(COLLECT(), 1)

or the second match:

=INDEX(COLLECT(), 2)

or the third:

=INDEX(COLLECT(), 3)

In your formula, you need it to look at your [Top 5 Reports] list and see if it's a duplicate number or not. If it's not a duplicate, then you only need the first match:

=INDEX(COLLECT(), 1)

But as soon as you have a duplicate, you need that number at the end of the formula to be dynamic and say, hey! This is the second time we've seen number 3. Instead of 1, lets skip that first match and go for the second:

=INDEX(COLLECT(), 2)

The way to do this is to use a COUNTIF to count how many times that [Top 5 Reports] number appears in your list.

 COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row)

The first range of the COUNTIF looks from the top row: [Top 5 Reports]$1 to the current row: [Top 5 Reports]@row

That results in a number. That number is what we want at the end of the INDEX(COLLECT function:

=INDEX(COLLECT(),  COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row))

For a full formula of:

=INDEX(COLLECT([ATA Chapter]:[ATA Chapter], Total:Total, [Top 5 Reports]@row), COUNTIF([Top 5 Reports]$1:[Top 5 Reports]@row, [Top 5 Reports]@row))

Cheers!

Genevieve

Marcus99

OMG finally got it!! Thank you so so soooo much for your help!! :)