I'm trying to dynamically sort a list of values for a helper column that looks something like this. I want to return the row number of the smallest value, then the next smallest, etc. The column on the right is what I want my output to look like. The formula I have so far is =ROW(SMALL(A$1:A$15, A$2:A2) but excel won't let me enter it.
In Excel O365 you could use a single formula in C1 using SORTBY:
=SORTBY(IF(A:A<>"",ROW(A:A),""),A:A,1)
In B1 enter 1. In B2 enter:
=IF(A2="",9999,IF( COUNTIF($A$1:A1,A2)=0,A2,A2+COUNTIF($A$1:A1,A2)/100))
and copy downward.
In C1 enter:
=MATCH(SMALL(B:B,ROWS($1:1)),B:B,0)
and copy downward:
NOTE:
Column B de-conflicts duplicate items in column A and handles the blanks in column A.
To limit the length of column C, use this instead in C1
=IF( ROWS($1:1)>COUNT(A:A),"",MATCH(SMALL(B:B,ROWS($1:1)),B:B,0))