Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

rank large value by different range and without duplicate


Hello, I have completed the following sheet to some extent and it does the ranking for a range

But I don't know what to do for some range

These ranges are in a column m  in sheet 2403, but since there is a sum of them at the end of each range, the entire column cannot be selected for ranking.

For this reason, I have to use some range


Help me please



Please see revised Answer/ file
John_Ru (rep: 3632) Jul 7, '22 at 1:22 pm
Add to Discussion


Selected Answer

Hi Kar2rost and welcome to the Forum.

This answer ahs been revised now that you have attached a file (as an Answer- it should be part of your question really). Usrs should note that you use right to left columns.

Your question is actually about using a complex formula to return the highest ranked number (without duplicates) but for multiple ranges. In J33, your formula for a single range is:


I suggest you use LARGE anstead, combined with COUNTIF to eliminate duplicates.

In the attached revised file, cell N38 (green)  has the range containing all values (and headings- which LARGE will ignore):


In cell K3, we get the highest value (ranked 1st) from that range using:


If we changed the second argument (k, the rank position) from 1 to a 2, we'd get the same again (since that value is duplicated). Instead, we count how many duplicates of K33 are in the range then add 1. The second argument becomes (in bold):


then copy that down and all duplicates will be ignored.You can either remove them from your range (e.g put them in anopther column) or adjust the formula in K33 to ignore them i.e.


Hope this works for you (please remember to mark this answer as Selected if so)



tanks John_Ru  
The problem is that the range '2403!m38:m214 have sum cell and cannot be used as the largest number (red lines in sheet 2403) and must be ignored
or I must use the specified range

2403!m38:m88 2403!m94:m145 2403!m163:m214 2403!m221:m272

And I have the number of ranks in the yellow cell, which ranks the largest numbers as desired (G29)
kar2rost (rep: 4) Jul 7, '22 at 1:31 pm
Yes I realise about the sum lines (and offered you a solution at the end of my answer).

I'm mobile now so don't understand your comment:

And I have the number of ranks in the yellow cell, which ranks the largest numbers as desired (G29) 

Please explain and I'll try to look tomorrow 
John_Ru (rep: 3632) Jul 7, '22 at 3:03 pm
in G29 i choose how many numbers are listed from big to small
Either I should write a formula that reads the data from the 2403 sheet in  this range 2403!m38:m88 2403!m94:m145 2403!m163:m214 2403!m221:m272 Exactly, or I should dynamically separate this range 2403!m94:m145 2403!m163:m214 2403!m221:m272in the 2403!m38:m88 to other sheet and give the whole column to the function in the new sheet to perform the ranking operation.
or should I write a formula that ranks the numbers in the adjacent cell with the a,b,c,d type without repetition and to a specified number
kar2rost (rep: 4) Jul 7, '22 at 4:09 pm
On using G29 to define lenght of ranked numbers list, change the formula in K34 to:
=IF($G$29>=D34,LARGE(INDIRECT($N$38),COUNTIF(INDIRECT($N$38),">="&K33) + 1),"")
apply some conditional formatting (for fill and borders) then copy down.

I'm afraid I probably won't have time today to look at anything else- I have to leave home now. Will add more when I get chance.
John_Ru (rep: 3632) Jul 8, '22 at 9:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login