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.

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

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.

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.

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.