How to Rank Duplicate Values in Excel without Skipping Numbers (Top 3 Report with Duplicates)
VloĆŸit
- Äas pĆidĂĄn 11. 07. 2024
- Join 400,000+ professionals in our courses here đ link.xelplus.com/yt-d-all-cou...
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.
âŹïž DOWNLOAD the workbook here: pages.xelplus.com/rank-values...
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:
Excel TextJoin Function - âą How to Solve Complex L...
Excel Lookup Formulas Playlist: âą Excel Lookup Formulas
â My Online Excel Courses www.xelplus.com/courses/
âĄïž Join this channel to get access to perks: / @leilagharani
đâ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
đ Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
đ„ RESOURCES I recommend: www.xelplus.com/resources/
đ©Letâs connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Grab the file I used in the video from here đ pages.xelplus.com/rank-values-file
A 4 year old vid saves the day!!!! Thanks Leila. You remain an absolute LEGEND FOREVER!
Thank you so much for all of your videos, especially this one. I'm using this set of functions most days!
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.
Thanks for the kind feedback Daniel!
Amazing Leila you always seems to go a few steps deeper into the matter, thank you for this one.
Thank you Leila! This approach to the problem was simply mind blowing
THE AMOUNT OF TIMES YOU SAVED MY DAY. YOU'RE THE BEST!
BEAUTIFUL! Great solution!
Your videos are awesome! I rely so much on your expertise to survive in my job! Thanks loads :)
Very useful - and impressive! Thank you!
Precise and concise, well explained, Leila
Very neat, clean-cut and handy formulas elaborated in a crisp-clear lecture. Perfect as usual, Leila!
Thanks for the kind words. I'm glad you like the video.
Great examples...thanks for the video!
That is so awesome and I will be putting it to use immediately. Thank you Leila.
Beautiful formula, thank you so muchđ
You are simply great and just brainstorming thanks again!
everything in your channel is very interesting. I'm using this tool as part of my work and requirements as a teacher, but i never had any idea of the other functions. Thank you for sharing your knowledge online.
excellent way to do the rank without skipping numbers.. I was searching for this exact formula.. Thanks a lot..
That is pretty advanced. Seriously who would've thought something like that could've been done, I love it!! Thank you Excel Queen.
You're welcome Mahmoud :) Soon we'll have it easier with dynamic arrays...
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.
Yes :) It does take some time to digest.....
beautiful mind .. superior teacher . big "thanks" , Leila .
This was exactly what I needed. Thx
Brilliant! Thanks Leila!
Hello Leila, thank you for this video, this is great.
You are brilliant! Clear explanation.
A very helpful video! Thank you for this one.
Very interesting and useful.Thanks!
Very useful . Thank you for this video.
Wow, I'd never come up with a solution like that. Amazing! Thank you so much
You're very welcome Affonso. Thank you for dropping by :)
Productive session,Thank you
love u for this leila , i really dint find a better explantion than this
brilliant, Leila, you have just saved my life - again... :D
Exactly what I'm looking for. Thank you my Queen
You are so welcome.
Awesome work.i really impressed by your work.
Brilliant and thank you.
Great work!
THANK YOU again and again
Thanks a ton.. I was looking for this for a long. U helped me a lot
Thank you for all your ideas..... queen
exactly what i needed within 2 minutes thanks very much
Thanks. Its very useful.
very informative.. Thanks
Mind-blowing logic !
This is a great formula!
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
Thank you Michael - Agree, the unique rank formula is great to avoid duplicate numbers. I use that when creating bar charts that are automatically sorted. Thank you for sharing.
This is brilliant .. thanks Michael.. any tips on ascending rank?
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!!
Thank you Wayne for your kind words and the thumbs up. Dynamic arrays would make this so much easier. I'll have to do a version on that one too :)
Thank you this is super helpfulđź
Excellent!!! It worked for me!!! Thank you!!!
Great!
Amazing!! Keep up the good Work Leila :)
That is slick and ending part with visualization is astonishing. thanks for sharing
Thanks Haider. I'm glad you like that part.
AWESOME .... YOU ARE GREAT
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
I recently found your channel. Your explanations are very clear, your example are well thought of and relevant for many applications. Keep up the EXCELLENT work of spreading knowledge !!
Thank you for the kind feedback Dane!
your videos are the best
Again another awesome presentation thanks!!!
Thank you Nissim. Glad you like it :)
Your video is very supporting and u are genius.
I have so many uses for this! Thank you for this video!
I'm glad you can put this to use :)
Thank you so much.......... i spent many hours just to find this
Easy and Nice Amazing Tips, before I used =LARGE() formula but duplicate was my nightmare, Leila, thanks for sharing this video!
Wow... Great đđđđđđđ đđđđ
very useful video....very .thanku mam
Thank you very Much for your coaching and guidance. It is real and It works
I'm glad the tutorials are helpful Olivier.
thank you so much Ms. leila. Thank you so much i have been searching that.
You're welcome đ
I have been looking for this video since 2017. Thanks to you. I'll definitely subscribe your channel.
Glad I could help with that. Great to have you here.
Really nice.
It took me a whole year to find out this solution. Thanks a lot
Thanks I did not realize how RANK.EQ related to RANK. Will use the former going forward.
great work Leila
Fantastic!
Excellent tutorial. Thanks
Glad you like it.
You are brilliant...
Very valuable video
Amazing!
shandar great keep it up
Hi Leila. it was good what u explained.
Thanks for the video, as always you are awesome with excel. Just wanted to know is there any other alternative for Textjoin function in excel 2016?
really awesome...
àžàžàžàžàžžàžàžàžŁàž±àž good and smart .
very useful, very thanku mam...
Awesome formula nice mam...
Mind-blowing ma'am
this is exactly what I want.. thanks a million
My pleasure. Glad you like it.
Awesome video thank you very much for your great videos. I have learned a lot. One thing though that I am struggling with is converting this sumproduct formula to a VBA Function. Would you have any pointers or would you make a video on how to convert this formula into a VBA function?
That's a good way of ranking, especially the use of sumproduct and countif especially when ranking the top 3, this method will not leave out individuals with the same rank
Thanks Solomon. Yes - using the usual Vlookup would get the first match - Textjoin is great for this. In the future with dynamic arrays, the formula and the steps will also get more simple :)
I have been following your channel for some time now and you offer excellent solutions Leila and for that I am grateful . I have wanted to use a function which does the following: as you enter numbers in a list it identifies that for example an 8 occurred.. a six occurred..(this part you covered very well in unique numbers )..... but as soon as a 7 occurs then all previous occurrences are reset without affecting the previously input list of numbers. Is this possible being that Excel is not time sensitive (sort of)?
more than Great â„
Thanks a lot!
You're very welcome.
nice work
You just put a big smile to my face :))
I'm glad to hear that :)
Why you donÂŽt have ten million followers is beyond me. Love your work, kick ass as usual. Keep it up. :)
That's very kind Mattias :)
Excellent..
Thanks
Understood the power and potential of using TEXTJOIN function better through this video. And SUMPRODUCT as always never fails to amaze!
TextJoin is one of those hidden super functions :) Thanks for dropping by Sandip.
Brilliant...you are so clear in your explanations......but I am still a bit dizzy after that !!âŠ..Very Good Tutorials
Glad you like it Victor. I hope the dizziness was not too bad :)
Thanku đ
Thanks for doing this Leila, it is very useful. I have a non-contigious range. How can I do the COUNTIF part for non-contigious ranges? Thanks
Match( Analytical skills, Creativity) = brilliant solution
Very interesting & useful
Thank you Piyush. I'm glad you like it!
You solve me those problems which CHT Gpt couldn't....đ
Happy to help :)
Nice đ madam
Thanks.....
I'm gonna have to get my hands dirty and do some testing to fully understand your ranking function. I love it. Thank you!
Getting hands dirty is the best way to learn :) Thanks Luciano.
Make a sample worksheet and test out the ideas.