Excel Top 10 List with Duplicates

For different purposes, you may need to find the top n number of values in Excel, n can be 5 or 10 and many more. Today we are going to show you how to find the top 10 values from a list with duplicates. For conducting the session, we are using Excel 2019. You can choose your preferred version.

Before diving into the session, let’s get to know about today’s workbook which is the base of our examples.

Excel sheet - Excel Top 10 List with Duplicates

We have a basic table, consisting of the sites and their respective number of visits per month. Using this relationship, we will see how to find the top 10 values.

There are a couple of duplicate values in the second column, they are stored on purpose.

This is a basic table to show you simple examples, you may encounter a much larger data set in the real-life scenario.

Practice Workbook

You are welcome to download the workbook from the link below.

Top 10 List with Duplicates

1. Using Formula

I. Combination of LARGE and INDEX – MATCH formula

Whenever you see the word ‘top’, you might have understood that one function we are going to use is the LARGE function.

Since our agenda is to find the top 10 values, let’s set the rank numbers (1 to 10).

set rank - Excel Top 10 List with Duplicates

Our scenario is such that we will fetch the top 10 visited sites.

To do the task we need to use the LARGE function. The LARGE function returns numeric values based on their position in a list when sorted by value.

LARGE(array, k)

array: The array from which you want to select the kth largest value.

k: An integer that specifies the position from the largest value.

We will provide the position using the k. When we need the top 10 value, then the value of k will be 1 to 10.

Write the formula in Excel.

LARGE - Excel Top 10 List with Duplicates

Here we have inserted the visits amount column as the array and the Rank as the k. Here we have found the most visited site.

Write the formula for the rests.

Autofill results - Excel Top 10 List with Duplicates

We have found the top 10 most visited sites from our list.

Now, do you think, if we conclude our task here will that match an ideal standard?

No, in most cases it will not. We need to find the items/names which is at the top.

To find the names we need to use the combination of the INDEX-MATCH functions.

INDEX(find_array,MATCH(lookup_value,lookup_array, 0))

The MATCH function is used to locate the position of a lookup value in a row, column, or table.

The INDEX function returns the value at a given location in a range or array.

Write the formula in Excel.

MATCH - Excel Top 10 List with Duplicates

Inside the INDEX function, we have inserted the Site Name column as our find_array. The MATCH function for declaring the row number.

Within the MATCH function, we have inserted the lookup_value and lookup_array. Our lookup_value is the visit value we have derived. 0 for stating Exact Match.

INDEX- MATCH result - Excel Top 10 List with Duplicates

The formula gave the result.

Now write the formula for the rest of the rows.

Error in result - Excel Top 10 List with Duplicates

Oh! Not the items we were looking for.

Our table has a number of duplicate visits. A couple of sites have the same amount of visits. That’s fine. But our formula should derive both the names. But this formula is unable to do that.

So what can we do? Do we need to modify the formula?

Obviously yes. We need to modify the formula.

Our modified formula will be

INDEX(find_array,MATCH(1, compare_value*check_already_matched,0))

Write the formula in Excel.

Modified formula - Excel Top 10 List with Duplicates

In the MATCH function, we have set 1 as the lookup_value. 

Then we have compared the value using $B$4:$B$19=LARGE( $B$4:$B$19, E4). This will return an array of TRUE or FALSE. 

Write this portion in Excel

compare - Excel Top 10 List with Duplicates

Press the F9 key.

True false array - Press the F9 key.

You will see an array of TRUE or FALSE. 

Now the COUNTIF function with an expanding range reference checks if a given item is already in the top list. It also returns an array of TRUE or FALSE. 

COUNTIF - Press the F9 key.

Press the F9 key.

COUNTIF true false - Press the F9 key.

When we multiply these two arrays then another array of 1s and 0s is returned.

Multiply arrays -Press the F9 key.

Press the F9 key now.

Array result - Excel Top 10 List with Duplicates

The lookup_value 1 matches with this array and returns the relative position.

Write the entire formula and press CTRL + SHIFT + ENTER

Result - Excel Top 10 List with Duplicates

Write the formula for the rest of the rows.

Autofills - Excel Top 10 List with Duplicates

Worked perfectly and provided what we wanted.

II. Combination of SMALL and INDEX Functions

We can use the combination of the SMALL and INDEX functions as well.

But before that, let’s set the rank dynamically.

Dynamic rank - Excel Top 10 List with Duplicates

We have a placeholder Top for how many values we want to see.

We will set such a way that the row number will fetch until it becomes higher than the number of values we want to see.

Formula for dynamic rank-Excel Top 10 List with Duplicates

We have used the IF function to do that.

Now fetch the top 10 values.

Large with if -Excel Top 10 List with Duplicates

The formula is similar to the previous one. In addition, we have added an IF statement there to eradicate the error value for empty cells.

Autofill - Excel Top 10 List with Duplicates

Now our formula to find the items will be

INDEX(find_array,SMALL(IF(looup_array=lookup_value, ROW(find_array)-ROW(first_row)+1, COUNTIF(lookup_value_array,lookup_value)))

Use the IF statement as previous to eradicate any error for the empty cells.

SMALL - Excel Top 10 List with Duplicates

Using the SMALL function, we find the row_number for the INDEX function. Inside the SMALL function, we have an IF function that checks whether the lookup_value is within the array or not.

The ROW functions are used to generate the row numbers starting from 1.

The COUNTIF function for checking whether the value has already been traversed or not.

The combination of the result from these functions produces a row_number that is needed to be fetched from the array.

Since it’s an array function, hit CTRL + SHIFT + ENTER to execute the formula.

Result of SMALL formula - Excel Top 10 List with Duplicates

We have found the Site name which topped the list.

Write formula or exercise Excel Autofill for the rest of the values.

Autofill small - Excel Top 10 List with Duplicates

III. Using AGGREGATE Function

We can use the AGGREGATE function as well for our task. If you are using an Excel version higher than 2007, then you can use this function.

The AGGREGATE function returns an aggregate calculation like AVERAGE, COUNT, MAX, SUM, etc.

The syntax for the AGGREGATE function is as follows

AGGREGATE(function_number,behavior_option, range)

function_number: This number specifies which calculation should be made.

behavior_option: Set this using number. This number denotes how the function will behave.

range: Range you want to aggregate.

There are a total of 19 operations, specified by the predefined function_number available in the AGGREGATE function. We are listing few frequently used function numbers

Function Function_number
AVERAGE 1
COUNT 2
COUNTA 3
MAX 4
MIN 5
PRODUCT 6
SUM 9
LARGE 14
SMALL 15

There are 8 possible values for behavior_options. They are

Value Behavior
0 Ignore SUBTOTAL and AGGREGATE functions
1 Ignore hidden rows, SUBTOTAL and AGGREGATE functions
2 Ignore error values, SUBTOTAL and AGGREGATE functions
3 Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions
4 Ignore nothing
5 Ignore hidden rows
6 Ignore error values
7 Ignore hidden rows and error values

We are leaving the link to the Microsoft Support site for the AGGREGATE function, which will help you know the function much deeper.

We need the SMALL functionality, so we will use 15 as function_number.

Our formula will be

INDEX(find_array,AGGREGATE(15,6,(ROW(find_array)-ROW(first_row)+1)/(lookup_array=lookup_value), COUNTIF(lookup_value_array,lookup_value)))

Write the formula in Excel.

AGGREGATE - Excel Top 10 List with Duplicates

If you have understood the earlier formulas then the only question that will be on your mind is why did we use 6 as behavior_option?

Can you figure out why?

Let’s help you a bit. Write the ROW division portion in Excel

ROW - Excel Top 10 List with Duplicates

Press the F9 key. Always when you want to see the insights, just hit F9 there.

ROW result - Excel Top 10 List with Duplicates

A bunch of division errors within the array. Which number can we use for ignoring errors?

Yes, to ignore the error, we have used 6 as our behavior_option value.

To execute this formula you need to hit ENTER only.

AGGREGATE result - Excel Top 10 List with Duplicates

Write the formula for the rest of the rows.

Autofill AGGREGATE - Excel Top 10 List with Duplicates

2. Using Pivot Table

Apart from using the formula, we can use the Pivot Table to find the top 10 values from a list.

First of all, select the entire table and then explore the Insert tab, you will find the Table section.

Within the Table section, there will be an option called Pivot Table  Click that.

Pivot Table - Excel Top 10 List with Duplicates

A dialog box will open in front of you.

Dialog box - Excel Top 10 List with Duplicates

Check the range and click OK (you can simply hit ENTER as well).

Pivot Table sheet - Excel Top 10 List with Duplicates

A new sheet will open like the above image.

Now Drag the Site Name to the Rows and the visit value column to the Values. 

Pivot Table row value - Excel Top 10 List with Duplicates

Usually, the Pivot Table sums up the numeric values. That’s not a contender for our concerns.

Click the Filter icon beside the Row Labels.

Select top 10-Excel Top 10 List with Duplicates

Now inside the Value Filters, you will find the Top 10 option. Click that.

Dialog box - Excel Top 10 List with Duplicates

A dialog box will pop up. Click OK 

Pivot Table output - Excel Top 10 List with Duplicates

You will find the top 10 values from your list.

Conclusion

That’s all for the session. We have tried listing several ways to find the top 10 lists with duplicates in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods which we might have missed here.


Further Readings:

Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo