So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

Try this array formula** :

rng = A$2:A$10

=IF(ROWS($1:1)<=SUM((rng<"")/COUNTIF(rng,rng&"")),INDEX(rng,SMALL(IF(rng<"",IF (ROW(rng)-MIN(ROW(rng))+1=MATCH(rng,rng,0),ROW(rng)-MIN(ROW(rng))+1)),ROWS($1:1))),"")

Copy down until you get blanks

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Javier Diaz" wrote in message
...
So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on
this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

Hi

No need for any formulae to achieve this.
Place your cursor in the cell where you want your results to begin.
DataConsolidateselect range of your data A2:B10tick Use labels in Left
ColumnOK

--
Regards
Roger Govier



"Javier Diaz" wrote in message
...
So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on
this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

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))

Copy B2 and paste into B3 and down as far as you need.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Ron Coderre" wrote:

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

Thanks a million for your help. Let me put it to the test and see how it
works. Thanks again.

"Ron 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))

Copy B2 and paste into B3 and down as far as you need.

Does that help?
***********
Regards,
Ron

XL2003, WinXP


"Ron Coderre" wrote:

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

"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.

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.

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.

"Ron Coderre" wrote...
....
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)),"")
....

Doesn't quite do the same thing. I had assumed your earlier formula

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

included the &"" bits in order to exclude blank cells. Now that I've tested
it, I see it doesn't. Neither does your latest formula above. Maybe it's the
correct thing to do to include blank cells in the results, but both your
formulas would then skip the last nonblank entry in col A. My 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)),
"")

returns all nonblank entries in col A. If it should include blank entries,
then change it to

=IF(COUNT(MATCH(A$2:A$20&"",B$2:B2,0))<MIN(ROWS(A$ 2:A$20),
COUNTA(A$2:A$20)+1),INDEX(A$2:A$20&"",
MATCH(0,1-ISNA(MATCH(A$2:A$20&"",B$2:B2,0)),0)),"")

"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.

Thanks, I was working on a different premise and missed the mid-list blanks
problem.

***********
Best Regards,
Ron

XL2003, WinXP


"Harlan Grove" wrote:

"Ron Coderre" wrote...
....
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)),"")
....

Doesn't quite do the same thing. I had assumed your earlier formula

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

included the &"" bits in order to exclude blank cells. Now that I've tested
it, I see it doesn't. Neither does your latest formula above. Maybe it's the
correct thing to do to include blank cells in the results, but both your
formulas would then skip the last nonblank entry in col A. My 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)),
"")

returns all nonblank entries in col A. If it should include blank entries,
then change it to

=IF(COUNT(MATCH(A$2:A$20&"",B$2:B2,0))<MIN(ROWS(A$ 2:A$20),
COUNTA(A$2:A$20)+1),INDEX(A$2:A$20&"",
MATCH(0,1-ISNA(MATCH(A$2:A$20&"",B$2:B2,0)),0)),"")

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.

Ok, I owe you Diner Ron. That worked perfectly. Wow, that was easy, I
should have come up with something like that, well, ok, it wasnt easy, but I
cant wait until I can come up with formulas like that on my own. Let me
study that puppy so that I can suck it right in. Thanks again for all of
your help guys!

"Ron Coderre" wrote:

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

Wow this is great, let me try this one. You guys are great! I'm having fun
over here. I was wondering what the Countif formula was doing.

"Ron Coderre" wrote:

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.

Thanks, Javier.....but, you should really consider switching to the formula
Harlan posted.

***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

Ok, I owe you Diner Ron. That worked perfectly. Wow, that was easy, I
should have come up with something like that, well, ok, it wasnt easy, but I
cant wait until I can come up with formulas like that on my own. Let me
study that puppy so that I can suck it right in. Thanks again for all of
your help guys!

"Ron Coderre" wrote:

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

I did Ron, I noted his comments, I'll be reviewing it to fully understand
it. I'll probably come back asking a question or two just in case I cant
find the logic in something. Harlan, Olive Garden on me! LOL.
"Ron Coderre" wrote:

Thanks, Javier.....but, you should really consider switching to the formula
Harlan posted.

***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

Ok, I owe you Diner Ron. That worked perfectly. Wow, that was easy, I
should have come up with something like that, well, ok, it wasnt easy, but I
cant wait until I can come up with formulas like that on my own. Let me
study that puppy so that I can suck it right in. Thanks again for all of
your help guys!

"Ron Coderre" wrote:

With
A2:A20 containing your data list, with A1 as a title

Try this:
B1: (any title)

This ARRAY FORMULA (committed with Ctrl+Shift+Enter) begins ths list of
unique items:
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))

Copy B2 and paste into B3 and down as far as you need.
Note: if B1 (the title) is empty, the formula returns 0 when it runs out of
uniques.

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Javier Diaz" wrote:

So guys, heres another one

Say in CElls A2:A10 I have values like so;

food
food
test
trial
trial
test

And in B2:B10 I have qtys. I want a total with no filter or pivot that
would return the unique entries into A12 like so

Food
Rest
Trial
Test

Thats about it, I'll take care of the sumif formula for the qy based on this
one thing that I reallly need. Pleaseeeeeeeeeeeee.

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.

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).

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).

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).

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).

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].

No, I've done the cntrol shift enter thing. Ithink we have broken excel with
these formulas. LOL. I wont give up with this one, something has to give.

"Harlan Grove" wrote:

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].

No, I've done the cntrol shift enter thing. Ithink we have broken excel with
these formulas. LOL. I wont give up with this one, something has to give.

"Harlan Grove" wrote:

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, here is a screenshot of the formula and a sample spreadsheet.

http://picasaweb.google.com/JavierDi...89505146200818

There I have cloned the formula side by side so that you can see that it
indicates March 2007 seven twice where it should read March then February.
And it the evaluation of the formula, it indicates that the result will be
February, but thats not the outcome.


"Harlan Grove" wrote:

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, here is a screenshot of the formula and a sample spreadsheet.

http://picasaweb.google.com/JavierDi...89505146200818

There I have cloned the formula side by side so that you can see that it
indicates March 2007 seven twice where it should read March then February.
And it the evaluation of the formula, it indicates that the result will be
February, but thats not the outcome.


"Harlan Grove" wrote:

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...
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?

"Javier Diaz" wrote...
I want to start off by thanking you a million times for your help.
....

You're welcome.

You here that Microsoft, . . .
....

From these newsgroups, MSFT hears nothing. Warning: rant coming.

MSFT has no excuse for your original formula

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

and my revision to it

=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)))

producing different results.

The first returns something like a range reference to a single cell, and the
second returns that cell's value. If that cell were K1, the simple reference
=K1 would have behaved the same as the result of the SECOND formula, i.e.,
it would have worked with your other formulas.

MSFT has NEVER provided a public explanation for the precise data type your
first formula returns. The 2 commas in sequence, producing a default 0 value
for the 2nd arg to INDEX makes INDEX return something that's not directly
usable. It's either not quite a range reference, or it's just a little more
than a range reference. Whichever, MSFT seems uninterested in documenting
what it is. So the rest of us (possibly excepting the MVPs, who may even be
subject to NDAs about @#$% like this) are left to speculate and to learn
that there are times one must wrap expressions in seemingly useless N, T or
CELL("Contents",..) calls.

Perhaps if the MVPs aren't subject to NDAs about this they could ask someone
on the Excel development team to explain the data type your original formula
returns, that is, if there's anyone on the Excel development team who knows.