put order of cell values in cells above-CodePudding

I have a row with random numbers and want to put their order (smallest first):

In this example, the random numbers are in row 2 and their order is in row 1. Note that columns A and C both have the same values and therefore have the same ranking.

I am not opposed to doing this as a function, but I am looking for an elegant solution.

CodePudding user response：

Turns out there is a rank function!

=rank(A2,$A2:$F2,TRUE)

CodePudding user response：

Using the RANK function, I believe you'll wind up with no rank of 4 in your sample scenario, given the two ties at rank 3.

If you don't care about that, you can use an array formula that will deliver all results at once. Clear all of Row 1 and then put this formula in cell A1:

=ArrayFormula(RANK(FILTER(2:2,2:2<>""),FILTER(2:2,2:2<>""),1))

This formula will deliver all results and it will continue to rank if more numbers are added into Row 2.

If you do care about missing rankings when there is a tie, you can still use an array formula; it's just a bit more complex. Again, clear all of Row 1 and place the following formula in A1:

=ArrayFormula(VLOOKUP(FILTER(2:2,2:2<>""),{SORT(UNIQUE(TRANSPOSE(FILTER(2:2,2:2<>"")))),SEQUENCE(COUNTA(UNIQUE(TRANSPOSE(FILTER(2:2,2:2<>"")))))},2,FALSE))

This is one of many approaches.

The section between the curly brackets { } will form an ascending sorted unique list of the numbers to the left of a sequence of numbers that starts at one and goes as high as the number of elements in the sorted unique list beside it.

FILTER considers only those cells in Row 2 (i.e., 2:2) that are not null, which will continue to expand as new cells are added.

VLOOKUP will look up each value in the first column of the virtual array held between those curly brackets and return the number in column 2 (i.e., the rank).

This formula assumes that any cells in Row 2 will be contiguous and will be real numbers. If for some reason that isn't the case, the following error controls will allow for gaps or non-numerical entries in Row 2 while still returning the rank of all numbers:

=ArrayFormula(IFERROR(VLOOKUP(2:2,{SORT(UNIQUE(TRANSPOSE(FILTER(2:2,ISNUMBER(2:2))))),SEQUENCE(COUNTA(UNIQUE(TRANSPOSE(FILTER(2:2,ISNUMBER(2:2))))))},2,FALSE)))