How to rank duplicate without skipping numbers in Excel?
In general, when we rank a list with duplicates, some numbers will be skipped as below screenshot 1 shown, but in some cases, we just want to rank with unique numbers or rank duplicate with same number without skipping the numbers as screenshot 2 shown. Do you have any tricks on solving this task in Excel?
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
If you want to rank all data with unique numbers, select a blank cell next to the data, C2, type this formula =RANK(A2,$A$2:$A$14,1)+COUNTIF($A$2:A2,A2)-1, and drag auto fill handle down to apply this formula to the cells. See screenshot:
If you want to rank duplicates with same numbers, you can apply this formula =SUM(IF(A2>$A$2:$A$14,1/COUNTIF($A$2:$A$14,$A$2:$A$14)))+1 in the next cell of the data, press Shift + Ctrl + Enter keys together, and drag auto fill handle down. See screenshot:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 months agoThis is showing the rank in ascending order. Can I get it to show in descending order?
- To post as a guest, your comment is unpublished.· 1 years agoThis formula will allow ties and not skip in ranks when scoring see cell F35 for the example
- To post as a guest, your comment is unpublished.· 1 years ago=RANK(A2,$A$2:$A$14,1)+COUNTIF($A2:A$3,A2)-1 This formula has a problem:
If the 3 first records are the same number the formula will generate a duplicate ranking for the Record #1 and Record #3 .
Example: 10 , 10, 10, 21, 4, 9 the ranking generated will be 3, 2, 3, 1, 6, 5 Ranking # is generated twice
- To post as a guest, your comment is unpublished.· 1 years agoDear Miguel, thanks for your pointing out mistake, I have updated the tutorial, you can try this formula =RANK(A2,$A$2:$A$14,1)+COUNTIF($A$2:A2,A2)-1
- To post as a guest, your comment is unpublished.· 1 years agoIt's a very nice example, but you inverted the ranking in the end... in the original rank (19) was the highest value and had a rank of 1. In your pair of formulas it made (10), which is actually the lowest number in the set rank 1.
How do you adjust the formula so (19) stays with a rank of 1?
- To post as a guest, your comment is unpublished.· 1 years agoHello, Joe Dirt, I do not get your problem clearly, could you give me more details?
- To post as a guest, your comment is unpublished.· 1 years agoThank You.
- To post as a guest, your comment is unpublished.· 2 years agoThe formula works if have to rank up to 100. From 100 skips to 102.
- To post as a guest, your comment is unpublished.· 2 years agoThank you for the formula. How do you keep it staying at zero when you don't type anything yet.
- To post as a guest, your comment is unpublished.· 2 years agoDo you mean keep blank cells display zero in general or when you count numbers?
- To post as a guest, your comment is unpublished.· 2 years agoThank you very much for sharing how to rank using consecutive numbers! It was just what I was looking for. Very counter-intuitive for sure, and I've written a few formulas that make your head hurt. This one made MY head hurt! :)