Creating Top 10 List Without Pivot Table

There can be times that you need to create top 10 lists that can be filtered independently from other worksheets along with many other tables and lists. In such situation your traditional pivot table solution will not work. But don’t worry, I will show you an alternative way of creating top 10 lists without pivot table.

Creating a top 10 list without pivot table is actually pretty easy. But creating a decent one needs some effort. I am going to create a monthly top 10 customers list that can be filtered by month. Here is the step by step introductions for how to do it:

This is our base data that contains monthly purchases. For our top 10 list, we need a customer based data.

Here is our customer based data. Purchases by month are calculated with a Sumifs Function in the last column. Not all customers made purchases each month, so don’t worry if you see zeros in this column.

Now let’s setup our top 10 list. I setup my workbook as shown below. Columns P to S will be hidden in final form.

Here you can see that I fetched top 10 purchases by using Large Function. I also went to some extra lengths to provide a nicer month filter.

Now we need to fetch customer names and surnames for each monthly purchase. This part is a little tricky because there might be more than one customers who made same amount of purchases. If we use regular lookup methods, we might end up fetching first name in the list for that purchase for each occurrence.

I will use the same method I used for Finding 2nd Matching value with Vlookup.

I will add a helper column to make purchase occurrences unique, so I can fetch right name each time. Here is my monthly purchases data with helper column added. As you can see, new column has purchase value with an occurrence counter attached to it.

I need to do same thing for my top 10 list too. Rest is typing a lookup formula into the name field. I used an Index+Match Formula since my helper column is the rightmost column. You can do this with Vlookup Function too. But your helper column should be located left of name column in that case. I’d really recommend learning Index+Match Formula though. It is an absolute life saver. Here is how it looks at this point:

Now that getting names for purchases is done, we need to fetch matching surnames. The reason and process is absolutely same with name field. Just repeat same helper column creations and your list is finished.

You can downlad and check finihed version of this list from here.