Hello Forum,
I need a Ranking formula to automatically eliminate duplicates in a dynamic range.
The standard Rank/Countif formula does not work with empty cells.
Ideally I'd like to specify lowest to highest or highest to lowest - if that's not possible, then lowest to highest is preferred.
I've attached a spreadsheet - conditional formatting highlights the duplicates.
Thank you for having a look - hopefully there is a solution
Bungaree
How about updating your file to show us what results you expect.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
remove duplicates ?
result in other column, sheet ?
maybe post exemple with desired result
Kind regards
Leo
Thank you for replying Tony and Leo, in col Q, instead of 2's I want 2, 3, 4, ie formula should look for duplicates or triplicates and apply consecutive numbers to them.
The result should be in column Q - which has my current basic rank formula, I just need the three 2's to come up as 2, 3, 4. The formulas extend beyond the range of data as the data range varies.
Kind regards,
Bungaree
Like this ?
Kind regards
Leo
Maybe this...
=IFERROR(RANK(T7,$T$7:$T$999,1)+COUNTIF(T$7:T7,T7)-1,"")
Instead of hiding your suggestion in a file post it directly in the reply where we can see it.Originally Posted by LeoTaxi
@ Tony
i do so with VBA
formulas by me is in dutch so i leave the translation to excel
nothing to do with hide
Kind regards
Leo
Fair enough!
Tony and Leo, you are awesome!!! Both work perfectly for me. Thank you SO much and thank you for your quick responses
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks