if it has to be an array formula anyway

It doesn't but I haven't tested to see if the non-array version is any
faster (but it's a few keystrokes longer which should be expected).

B2 [Topmost result cell]:
=A2

That assumes A2 is not an empty cell. Might be better to use a "lookup"
formula to get the first entry of the range.

Using =A2

In B3 normally entered:

=IF(ROWS($1:2)<=SUMPRODUCT((A$2:A$20<"")/COUNTIF(A$2:A$20,A$2:A$20&"")),INDEX(A$2:A$20,MATC H(0,INDEX((A$2:A$20<"")-ISNA(MATCH(A$2:A$20,B$2:B2,0)),,0),0)),"")

On a side note: have you ever encountered this? (I'm using Excel 2002 all
updates applied)

Open a *new* workbook.

Do not enter any data at this time.

Enter this formula in C1:

=SUMPRODUCT((A1:A10<"")/COUNTIF(A1:A10,A1:A10&""))

The formula correctly returns 0.

Now, enter something in A1.

The formula returns #DIV/0!

Keep entering data 1 cell at a time until you reach A10.

Now clear A1:A10 and then start entering data at random locations in the
range.

My "theory" is that this is related to a used range not being set that is
equal in size to the referenced range in the formula. The formula starts to
work as it should once an entry is made in A10 thus establishing a used
range that is = the referenced range in the formula. I ran into this
setting up a template a while back.

I've never experienced this behavior with *any* other formula.

--
Biff
Microsoft Excel MVP


"Harlan Grove" wrote in message
...
"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.