How to Rank Duplicate Values in Excel without Skipping Numbers (Top 3 Report with Duplicates)

Learn how to rank duplicate values in Excel without skipping numbers in the sequence. I also show you how the RANK function works and the difference between Rank.EQ and Rank.AVG functions. We also take a look at understanding how complex Excel array formulas such as SUMPRODUCT with criteria works by breaking down the formula.

This video covers the RANK function in Excel, providing clear guidance on ranking values in both ascending and descending order, including handling duplicate values without skipping numbers in the sequence.

🔑 Key Points:
- Ranking: Learn how to rank sales managers based on their sales numbers, handling scenarios where two managers have the same sales figure.
- Understanding RANK Function: Get to grips with the RANK and RANK.EQ functions, exploring their use for maintaining the original order of data while ranking in a separate column.
- Handling Duplicates: Find out how to rank duplicate values without skipping numbers, ensuring a continuous sequence in your ranking.
- Complex Formula for Ranking: Discover a more intricate formula involving SUMPRODUCT and COUNTIF, ideal for ranking without skipping numbers in the sequence.
- Creating a Top 3 Report: Learn how to generate a report showing the top three sales managers, including all those tied for a position, using the TEXTJOIN function.
- Detailed Explanation: Benefit from a thorough walkthrough of the formulas used, providing clarity on each step of the ranking process.

0:00 How to use the Excel RANK function
0:51 RANK Function & RANK.EQ
3:30 RANK duplicates but don't skip numbers in between
6:51 Top 3 Report
8:51 SUMPRODUCT & COUNTIF Excel Array formula explained

You might need to create a top 10 or top 3 report in Excel. For example you'd like to get the top 3 values but there are two categories that have the exact same value and both are considered number 2. How can you show both categories as number 2 and not just the first one? VLOOKUP will not help here, because it will return the first match. You'd like ALL matches returned. The solution uses the SUMPRODUCT function together with the Excel COUNTIF function to get the ranking. We then use the TEXTJOIN and IF functions together as an array to get the category names ranked in ascending order.

LINKS to related videos:

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[tosinis]

A 4 year old vid saves the day!!!! Thanks Leila. You remain an absolute LEGEND FOREVER!

[danielk]

Every time i need a tutorial from Excel, i check Leila's videos before i go to another channel, because she explains it so well. Not like other guys with one hella heavy accents who just uses random formula or whatever without explaining the logic then be like "Ta-da there you have it". Tbh im so grateful for Leila's channel.

[mikepitkanen]

Hello Leila,
Thank you for showing the Textjoin function when listing employees by rank - brillant!
As for ranking without duplicates, what I typically use (because I can work my head around it) is the following formula which would be in cell D5: =RANK.EQ(B5, $B$5:$B$24)+COUNTIF(B$4:B4, B5). Alternatively if the column headings were only numbers (or the data started in the first row) it would change to =RANK.EQ(B5, $B$5:$B$24)+COUNTIF(B$5:B5, B5)-1.

Love your tutorials! Thank you!

Michael

[mslimtradersserver]

Respected Madam, For ranking purpose i watch dozen of videos in past, so i watch your video today and i find my problem of ranking without skipping number, so you are a great and i am thankfull to you for making this video, be sure this video solve my problem which i facing from 6 years, thank you so much

[mienzillaz]

I had to give it a moment before i fully understood what is going on. So you're valuating entries bigger then currently checked but with proportional contribution.

So clever, i love it.

[jujuvisnadi]

I watched this video a while a go and it blew my mind. Today I had to create a ranking and realised I didn't want to skip positions, so came back to watch it again. Thank you!!
My question is if I want to add another criteria in order to avoid repeating positions (and not skip positions as well). I can't join both tricks unfortunately.

[excelgazialimuhiddinhacibekir]

Very neat, clean-cut and handy formulas elaborated in a crisp-clear lecture. Perfect as usual, Leila!

[moxie]

For people who do not have Textjoin, an alternative would be to make a sequential list of 1-20 beside column D unique rank. Then in the report tab in cell D6 enter =INDEX(Data!$A$5:$A$24, MATCH(Report!C6, Data!$E$5:$E$24, 0)

[andianugerah]

THE AMOUNT OF TIMES YOU SAVED MY DAY. YOU'RE THE BEST!

[victorcox]

Brilliant...you are so clear in your I am still a bit dizzy after that !!…..Very Good Tutorials

[mahmoudbasheti]

That is pretty advanced. Seriously who would've thought something like that could've been done, I love it!! Thank you Excel Queen.

[wayneedmondson]

Hi Leila.. great video! Thanks for the SUMPRODUCT, COUNTIF and TEXTJOIN fun!! Before enrolling in your classes and watching your channel, I would have been clueless as to what you did here. Thanks to your great instructions and clear explanations, I can now easily follow along and understand your approach, as well as replicate your solutions and extend them to other uses. For anyone wanting a deep dive into these great techniques, I strongly recommend that they seek out all your offered resources.. both free and paid. You won't be disappointed and you will up your game in EXCEL. In recreating your example, I realized that you might want to sort the results for the ranks with multiple answers. With DAFs, that would be easy.. wrap the TEXTJOIN function with SORT().. yes? Can't wait until those are released by MS!! Thanks again for all that you offer to the EXCEL community. Your contributions and resources are invaluable. Thumbs up!!

[IanLockley]

Thank you so much for all of your videos, especially this one. I'm using this set of functions most days!

[ptiwari]

You solve me those problems which CHT Gpt couldn't....😊

[JaskaranSingh-rwyh]

excellent way to do the rank without skipping numbers.. I was searching for this exact formula.. Thanks a lot..

[m.hussainbarahvi]

Dear Mam,
I have no words to thank you. Super trick.
I was in trouble for the last two years while developing the result sheets of my school and I was unable to find a way... You are genius. May God bless you.

[cuoivelo]

Exactly what I'm looking for. Thank you my Queen

[szisziszilvi]

brilliant, Leila, you have just saved my life - again... :D

[MrHorzel]

Amazing Leila you always seems to go a few steps deeper into the matter, thank you for this one.

[AviationAPassion]

It took me a whole year to find out this solution. Thanks a lot