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.

"T. Valko" wrote...
....
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.
....

Fair point. It should be

B2 [array formula]:
=VLOOKUP("?*",A2:A20,1,0)

if col A contains only text, or

=INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0))

if col A could contain anything.

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,MATCH(0,INDEX((A$2:A$20<"")
-ISNA(MATCH(A$2:A$20,B$2:B2,0)),,0),0)),"")

It'll be slower. The COUNTIF call goes through the full col A range aginst
itself in each formula, while my formula only goes through col A against the
previous cells in col B in each formula. Also, the extra INDEX call needed
to avoid array entry wouldn't help recalc speed.

On a side note: . . .

Works in Excel 2003. IIRC, this was something MSFT documented as fixed in
XL2003. Your diagnosis is correct. So upgrade already.

Hello there Harlan,

Harlan, everything worked for text values. It worked so great I decided to
use this formula for another data range, dates generated from a lookup. And
its not working the same anymore, it throws back not only duplicates, but
even if theres only 1 entry, where ever this formula exist, it just repeats
that one entry. Please help.

This is the formula I'm using. I played around with it for about three
hours with all sorts of other nesting to fix it before I asked, but I couldnt
figure it out. Remember, we're messing with dates that are lookedup not hard
keyed.

=IF(COUNT(MATCH(H$20:H$44,C$48:C48,0))<COUNT(1/(H$20:H$44<"")),
INDEX(H$20:H$44,MATCH(0,(H$20:H$44<"")-ISNA(MATCH(H$20:H$44,C$48:C48,0)),0)),
"")

"Harlan Grove" wrote:

"T. Valko" wrote...
....
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.
....

Fair point. It should be

B2 [array formula]:
=VLOOKUP("?*",A2:A20,1,0)

if col A contains only text, or

=INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0))

if col A could contain anything.

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,MATCH(0,INDEX((A$2:A$20<"")
-ISNA(MATCH(A$2:A$20,B$2:B2,0)),,0),0)),"")

It'll be slower. The COUNTIF call goes through the full col A range aginst
itself in each formula, while my formula only goes through col A against the
previous cells in col B in each formula. Also, the extra INDEX call needed
to avoid array entry wouldn't help recalc speed.

On a side note: . . .

Works in Excel 2003. IIRC, this was something MSFT documented as fixed in
XL2003. Your diagnosis is correct. So upgrade already.

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

Alright Harlan, sorry to take up your time. This actually the lookup formula
for those dates -
=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1! $H$1:$N$1,MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<"",0))
It seems this formula is making your formula go crazy for some reason. I'm
looking into it.

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

"Javier Diaz" wrote...
Alright Harlan, sorry to take up your time. This actually the lookup
formula for those dates -

=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1 !$H$1:$N$1,
MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<"",0))

It seems this formula is making your formula go crazy for some reason.
I'm looking into it.
....

Is there any particular reason you're skipping the 2nd arg in the first
INDEX call rather than just using 2 arguments?

Since Sheet1!H1:N1 would be dates, so numbers, try this formula instead.

=N(INDEX(Sheet1!$H$1:$N$1,MATCH(TRUE,INDEX(Sheet1! $H$1:$N$6479,
MATCH(A1,Sheet1!$G$1:$G$6479,0),0)<"",0)))

If this doesn't work, what's in A1 and Sheet1!G1:G6479?

Harlan, I want to start off by thanking you a million times for your help.
I dont know how I can repay you. That formula you adjusted for me worked
like a charm. It's truely unbelievable that you can do such a thing. Wow,
wow, wow, wow. The problem that I find so far, is that even though I get the
results I need, I cant seem to get around to understanding the formulas most
of the time, lol. I'll try to figure this one out. But Harlan, thanks a
million.

You here that Microsoft, Harlan rocksssssssssssss!

"Harlan Grove" wrote:

"Javier Diaz" wrote...
Alright Harlan, sorry to take up your time. This actually the lookup
formula for those dates -

=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1 !$H$1:$N$1,
MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<"",0))

It seems this formula is making your formula go crazy for some reason.
I'm looking into it.
....

Is there any particular reason you're skipping the 2nd arg in the first
INDEX call rather than just using 2 arguments?

Since Sheet1!H1:N1 would be dates, so numbers, try this formula instead.

=N(INDEX(Sheet1!$H$1:$N$1,MATCH(TRUE,INDEX(Sheet1! $H$1:$N$6479,
MATCH(A1,Sheet1!$G$1:$G$6479,0),0)<"",0)))

If this doesn't work, what's in A1 and Sheet1!G1:G6479?

Alright Harlan, sorry to take up your time. This actually the lookup formula
for those dates -
=INDEX(Sheet1!$H$1:$N$1,,MATCH(TRUE,OFFSET(Sheet1! $H$1:$N$1,MATCH(A1,Sheet1!$G$1:$G$6479,0)-1,)<"",0))
It seems this formula is making your formula go crazy for some reason. I'm
looking into it.

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

It returned 0

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

Harlan,

Funny thing is, that when I evaluate the formula, it says that the formula
result is the correct result that it should be, when I exit out of the
formula audit, it goes to the wrong result, the first entry in the range.

"Javier Diaz" wrote:

It returned 0

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

Javier Diaz wrote...
....
Funny thing is, that when I evaluate the formula, it says that
the formula result is the correct result that it should be, when
I exit out of the formula audit, it goes to the wrong result,
the first entry in the range.
....

That makes it appear that you're not entering the formula as an array
formula. Hold down [Ctrl] and [Shift] keys before pressing [Enter].

Javier Diaz wrote...
....
Funny thing is, that when I evaluate the formula, it says that
the formula result is the correct result that it should be, when
I exit out of the formula audit, it goes to the wrong result,
the first entry in the range.
....

That makes it appear that you're not entering the formula as an array
formula. Hold down [Ctrl] and [Shift] keys before pressing [Enter].

Harlan,

Funny thing is, that when I evaluate the formula, it says that the formula
result is the correct result that it should be, when I exit out of the
formula audit, it goes to the wrong result, the first entry in the range.

"Javier Diaz" wrote:

It returned 0

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

It returned 0

"Harlan Grove" wrote:

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

Javier Diaz wrote...
....
. . . Remember, we're messing with dates that are lookedup not hard
keyed.
....

So are they just dates or dates with times but formatted to show only
the date portion? Check using the formula

=SUMPRODUCT(SIGN(MOD(H$20:H$44,1)))

If this returns 0, then there are dates with times in H20:H44. If
you want to treat them as dates alone, change all references to H$2:H
$44 to INT(H$2:H$44).

Hello there Harlan,

Harlan, everything worked for text values. It worked so great I decided to
use this formula for another data range, dates generated from a lookup. And
its not working the same anymore, it throws back not only duplicates, but
even if theres only 1 entry, where ever this formula exist, it just repeats
that one entry. Please help.

This is the formula I'm using. I played around with it for about three
hours with all sorts of other nesting to fix it before I asked, but I couldnt
figure it out. Remember, we're messing with dates that are lookedup not hard
keyed.

=IF(COUNT(MATCH(H$20:H$44,C$48:C48,0))<COUNT(1/(H$20:H$44<"")),
INDEX(H$20:H$44,MATCH(0,(H$20:H$44<"")-ISNA(MATCH(H$20:H$44,C$48:C48,0)),0)),
"")

"Harlan Grove" wrote:

"T. Valko" wrote...
....
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.
....

Fair point. It should be

B2 [array formula]:
=VLOOKUP("?*",A2:A20,1,0)

if col A contains only text, or

=INDEX(A2:A20,MATCH(0,-ISBLANK(A2:A20),0))

if col A could contain anything.

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,MATCH(0,INDEX((A$2:A$20<"")
-ISNA(MATCH(A$2:A$20,B$2:B2,0)),,0),0)),"")

It'll be slower. The COUNTIF call goes through the full col A range aginst
itself in each formula, while my formula only goes through col A against the
previous cells in col B in each formula. Also, the extra INDEX call needed
to avoid array entry wouldn't help recalc speed.

On a side note: . . .

Works in Excel 2003. IIRC, this was something MSFT documented as fixed in
XL2003. Your diagnosis is correct. So upgrade already.

Wow, you guys are really cracking on this one. First thing tomorrow I'll
start adapting this formula to the workbook and see how it works. Thanks
Harlan, Ron, T.Valko for all of your help.

"T. Valko" wrote:

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.