Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Check COlumn - Excel VBA | Excel Discussion (Misc queries) | |||
creating a bar graph | Excel Discussion (Misc queries) | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
How to group similar column titles together???? | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions |