How to get nth distict value in a column
Bob Phillips wrote...
In B1: =A1
In B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""), 0)),"",
INDEX(IF(ISBLANK($A$1:$A$20),"",$A$1:$A$20),
MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)))
....
Compact form
B2:
=IF(SUM(COUNTIF(B$1:B1,A$1:A$20))<COUNTA(A$1:A$20) ,
INDEX(A$1:A$20,MATCH(0,COUNTIF(B$1:B1,A$1:A$20),0) ),"")
As for the general question of the n_th distinct value in a list (LST),
=INDEX(LST,SMALL(IF(MATCH(LST,LST,0)=ROW(LST)-ROW(INDEX(LST,1,1))+1,
MATCH(LST,LST,0)),n))
also an array formula.
|