I like where you went with that, Harlan.

I found more effiiciencies in the row reference of INDEX with this array
formula:
B3:
=IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),INDEX(A$2:A$20,MATCH(1,--ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"")

Copy B3 into B4 and down as far as needed.

That's what makes these groups so rewarding.
Just measuring the B3 formula length....
We went from my 150+ character "rough draft"
to your 136 characters
to the lastest 123 character formula

***********
Best Regards, Harlan

Ron

XL2003, WinXP


"Harlan Grove" wrote:

"on Coderre" wrote...
This slightly tweaked version ignores the title cell (B1)

ARRAY FORMULA
B2:
=IF(SUMPRODUCT(($A$2:$A$20<"")*1/COUNTIF($A$2:$A$20,$A$2:$A$20&""))<=COUNTA($B$1:$B 1&"")-1,"",INDEX($A$2:$A$20,MATCH(TRUE,
ISERROR(MATCH($A$2:$A$20,$B$1:$B1,0)),0),1))
....

Save some keystrokes. First, if it has to be an array formula anyway,
nothing gained using SUMPRODUCT. Second, nothing gained by *1/ rather than
just / . And <=COUNTA(..)-1 gives the same result as <COUNTA(..). But even
more of a calculation saver, you don't need to count the distinct entries in
col A against the total previous entries in col B; you could count the
number of matches in col A for previous entries in col B against the total
number of entries in col A.

B2 [Topmost result cell]:
=A2

B3 [array formula]:
=IF(COUNT(MATCH(A$2:A$20,B$2:B2,0))<COUNT(1/(A$2:A$20<"")),
INDEX(A$2:A$20,MATCH(0,(A$2:A$20<"")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),0)),"")

Fill B3 down.