What should be the Arrayformula at D1 to get the column D?

Viewed 28

enter image description here

Column A contains only unique value.

I have actually achieved this using two formulas:

(i) Arrayformula at B1:

={"ARRAYFORMULA 1";ARRAYFORMULA(if($A2:$A = "",,countifs(countifs($A2:$A, $A2:$A, row($A2:$A), "<=" & row($A2:$A)), 1, row($A2:$A), "<=" & row($A2:$A))))}  

(ii) Formulas at column B:

=INDEX(FILTER(B$1:B2,B$1:B2<>""), COUNTA(FILTER(B$1:B2,B$1:B2<>"")))  

(1) Why doesn't Arrayformula version of this formula doesn't work:

=Arrayformula(INDEX(FILTER(B$1:B2,B$1:B2<>""), COUNTA(FILTER(B$1:B2,B$1:B2<>""))))  

(2) I am not able to get a single Arrayformula to produce my result. What Arrayformula should I use at D1 to my desired results?

Google Sheet Link

1 Answers

try:

=ARRAYFORMULA(SORTN(COUNTIFS(COUNTIFS(A2:A, A2:A, 
 ROW(A2:A), "<="&ROW(A2:A)), 1, 
 ROW(A2:A), "<="&ROW(A2:A)), 
 MAX(ROW(A2:A)*(A2:A<>""))-ROW(A2)+1, 0, 1, 1))

enter image description here

Related