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

Join 400,000+ professionals in our courses here 👉 https://link.xelplus.com/yt-d-all-courses

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: https://pages.xelplus.com/rank-values-file

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 - https://youtu.be/TMZEUlFGp1U
Excel Lookup Formulas Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyxpMnh_KKfAgp5DF9ydawmi

★ My Online Excel Courses https://www.xelplus.com/courses/

➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

🎥 RESOURCES I recommend: https://www.xelplus.com/resources/

🚩Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.linkedin.com/company/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

Тэги:

#Excel_Rank_function #rank_without_skipping_numbers #RANK.eq #rank.avg #return_multiple_match_results_in_excel #Textjoin_function #excel_sumproduct #rank_function_with_duplicates #excel_textjoin_vlookup #Rank_and_Countif #excel_array_formulas #excel_arrays_explained #excel_sumproduct_with_criteria #XelplusVis #Excel_Tutorials #Leila_Gharani #Excel_2016 #Excel_2013 #Excel_2010 #Advanced_Excel_tricks #Excel_online_course #Excel_tips_and_tricks #Excel_for_analysts

Скачать видео