How do I create a unique ranking from an array/table of values?

How about this:

=MATCH(A1+(ROW(A1)/1000),INDEX(LARGE(A$1:A$9+(ROW(A$1:A$9)/1000),ROW(INDIRECT("1:" & COUNTA(A$1:A$9)))),0),0)

Originally Posted by Andrew-R

How about this:

=MATCH(A1+(ROW(A1)/1000),INDEX(LARGE(A$1:A$9+(ROW(A$1:A$9)/1000),ROW(INDIRECT("1:" & COUNTA(A$1:A$9)))),0),0)

Thanks, I'll give it a look. My real data is a much larger, more complicated data set. Out of curiousity, can you explain the gist of what the formula is doing and why the /1000 is needed?

So far it seems like this solution is extremely resource intensive... also, it didn't let me copy across columns, just down rows. Thoughts?

Instead of ranking just the numbers it's ranking the numbers + 1/1000th of the row they're on, so if your data was in rows 1:9 and each row had the value 10 in they've be ranked as 10.001, 10.002, 10.003 ... 10.009

The part of the formula beginning with INDEX puts those numbers in an array, arranged from the largest to the smallest and then the MATCH returns the current row's position within that array.

Adding Row/1000 is purely arbitrary - it just needs to be a number larger than the number of rows of data you have, to ensure that each value will be changed only by a value of <1.

Yes, it's probably pretty processor hungry, not sure why it won't let you copy across, though.

I got it to copy across... was a bug from something else I had open

Gotcha... so the data I'm ranking is money. So adding a tenth of a cent would be OK to me. Is the INDEX function resource heavy? Is there a way to use the RANK function once they've had the small bit of value added to them?

If my first row of data is in row 1137, do I change the 1: to 1137:?

Is the number of rows of data fixed? If so we can get rid of the INDIRECT statement, which won't be helping things at all.

Also, what if 2 numbers in 1 row are the same? they are having the same amount added to them (1/1000th of the row)... how do they get unique values?

Originally Posted by Andrew-R

Is the number of rows of data fixed? If so we can get rid of the INDIRECT statement, which won't be helping things at all.

yes, the data goes from row 1010 to row 1065 - and columns G to HX. It is fixed.

OK, try this:

=MATCH(A1010+(ROW(A1)/100000),INDEX(LARGE(A$1010:A$1065+(ROW(A$1:A$56)/100000),ROW(1:56)),0),0)

I've increased the divisor to allow for you using fraction values, and we don't need to add the row number the data is on, just the values of a block of rows the same size as the data.

A less processor intensive way of doing this might be to set up a mirror of the table on a hidden sheet, where we add the Row value, and then rank that hidden table. The hidden table will update as your main one does and should be easier than messing around with INDEX

Does this account for having the same value in the same row?

=MATCH(A1010+(ROW(A1)/100000),INDEX(LARGE(A$1010:A$1065+(ROW(A$1:A$56)/100000),ROW(1:56)),0),0)

This isn't quite making sense to me.... if my data starts in column G, shouldn't the A's be G's? and where does the HX column come into play which is the last column my data is in?

Ah, sorry, you're trying to rank the whole range - not just one column at a time?

That's going to complicate things.

Ah, DLL seems to have found a simpler way ... I'd go with that

Originally Posted by Andrew-R

Ah, sorry, you're trying to rank the whole range - not just one column at a time?

That's going to complicate things.

yes, the goal is rank the entire set of data together... I'm going to see if daddylonglegs response works...

OK DLL - I think you have it... but I wonder if 1 thing should be changed.

The second COUNTIF is looking at the column above the number we are checking as well as to the right. Because the first COUNTIF already looks above and to the left, should we have the second COUNTIF go up to the row above, but 1 column to the right of the number we are ranking? Does this make sense? Otherwise, we would be double-counting values when adding in the multiple COUNTIF functions.... thoughts?

Originally Posted by daddylonglegs

No, I believe it works "as is" - the first COUNTIF just looks to the left on the current row - the second COUNTIF looks at all the previous rows, so if you get the $ signs right you won't double-count anything - I can post a working example if you want

Yes, if you could please post the example that would be very helpful...

Maybe I have a mistake in my $...

DLL - sorry. You were correct, I had an error in my $ for the second COUNTIF - I thought it was the same as the row above, but the formula had changed. Sorry. Thank you for your help! Great work!

Originally Posted by daddylonglegs

Actually, although the previous suggestion works OK this version is better

=RANK(A1,$A$1:$I$9)+COUNTIF($A$1:$I1,A1)-COUNTIF($A1:A1,A1)

You can use that one formula for the whole table, whether there's a blank line above or not

The first COUNTIF looks at all values on current row and previous and the second COUNTIF subtracts everything to the right, thereby only including previous values.

See attached - columns N and O use COUNTIF to check that there's only one of each rank - press F9 to re-generate random numbers in table

It looks like you are subtracting everything to the left, no?

Originally Posted by daddylonglegs

Yes, you're right - I'm confusing myself now!

That will still work to get unique ranks but assuming you want equal values ranked in order left to right and top to bottom it should be like this

=RANK(A1,$A$1:$I$9)+COUNTIF($A$1:$I1,A1)-COUNTIF(A1:$I1,A1)

Thank you!!!