# RANK - Non unique values, contiguous rank required

1. ## RANK - Non unique values, contiguous rank required

Hi,

I'm trying to rank some values where more than one cell may contain the same value. Normally Ranking these would give these values the same rank which is fine, but the next value would not follow, but skip a few numbers:

Example:

Data Rank
30 2
10 6
20 4
20 4
30 2
50 1

What I would like it to do is:

Data Rank
30 2
10 4
20 3
20 3
30 2
50 1

Any suggestions would be very much appreciated.

Kind regards
Paul

2. Would this be an acceptable option?

This will rank each number sequentially and if a duplicate entry occurs it will give sequential numbers instead of repeating.

=RANK(\$A1,\$A\$1:\$A\$6)+COUNTIF(\$A\$1:A1,A1)-1

Where A1:A6 houses your numbers to rank. copied down

3. Thanks for that, though I do want it to give the same number rank for the same value (as rank would normally do), but after that I want it to follow on at the next number, and not jump x places. So if the data values are non unique, it means there will be less unique rank numbers than there are data values (as per example).

Cheers
Paul

4. Try in B1 copied down

=SUM(--ISNUMBER(MATCH(ROW(INDIRECT("1:"&RANK(A1,A\$1:A\$6))),RANK(A\$1:A\$6,A\$1:A\$6),0)))

confirmed with CTRL+SHIFT+ENTER

5. Here's another way I found here (by Harlon Grove):

``Please Login or Register  to view this content.``
Formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

6. Thankyou both for taking time out to look at this. I've tried both solutions, and both seem to be giving me the same value for every cell. I'm concerned that it may be my inability to paste arrays properly that may be the cause of the problem (highlight a number of rows, hit F2, Ctrl+Shift+Enter?), so I've uploaded a small sample of actual data so you can see what I'm trying to do (there are thousands of groups to be ranked, so I'm hoping a single array per sheet can handle that rather than having to paste them individually!)

Thankyou for you patience in this. I've always managed to find some way of getting excel to do what I want, and this is the first one that's beaten me.

Sincerely
Paul

7. Hi, you need to confirm the formula with CTRL+SHIFT+ENTER not just ENTER.You will note the {} brackets.

I've attached the sheet here and entered the formula (daddylongleg's version) in BR so you can compare. you can move it to BS at your convenience.

8. That does exactly what I was looking for. Thankyou so much!

Kind regards
Paul

9. ## Re: RANK - Non unique values, contiguous rank required

If I´m understanding what is going on that is exactly what I[m trying to get but I just can´t get to it.

I´m trying to find how to upload in here but I can´t find the option so... https://www.dropbox.com/s/mi5sv53fbn...%20Needed.xlsx

I´m trying to get column C (Rank) to rank column B (duh), but I cant have the skipping between numbers. Column C shows what i would need int his case since these are 12 different values.

I tried the options presented but could not make those work.

Any help?

10. ## Re: RANK - Non unique values, contiguous rank required

Hi, ellogan,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

===

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Ciao,
Holger

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1