Excel - assign the index of every value is a sorted array

Question

I know that the question may not really explain what I am asking about, so let me explain it here. Basically, I have a row in my spreadsheet, that contains i.e. 10 values. Let's say it looks like this:

0.60|0.60|0.60|1.40|0.90|1.05|0.00|0.00

Under this row, I have an empty row, and the problem is I have to assign to every of this value it's position in a reversely sorted array of those values (with no duplicates). In this particular case:

Sorted array: 0.00, 0.60, 0.90, 1.05, 1.40

So I would write down a row like this: 4|4|4|1|3|2|5|5| (indexing from 1)

Is there a way to write a formula for this kind of problem? Or do I have to do it manually?

Answer 1

Use this array formula:

=MATCH(A1,LARGE(IF(MATCH($A$1:$H$1,$A$1:$H$1,0)=COLUMN($A$1:$H$1)-COLUMN($A$1)+1,$A$1:$H$1),ROW($A$1:INDEX($A:$A,SUMPRODUCT(1/COUNTIF($A$1:$H$1,$A$1:$H$1))))),0)

Being an array formula one must put the formula in the first cell, confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode and then copy across.

The LARGE(IF(MATCH($A$1:$H$1,$A$1:$H$1,0)=COLUMN($A$1:$H$1)-COLUMN($A$1)+1,$A$1:$H$1),ROW($A$1:INDEX($A:$A,SUMPRODUCT(1/COUNTIF($A$1:$H$1,$A$1:$H$1))))) returns the sorted array and the MATCH will find the numbers location in that array.

Adjust all the ranges to your dataset EXCEPT ...ROW($A$1:INDEX($A:$A,... These are counters and must remain as is regardless where the dataset is located.