Return Unquie Values

[Guest]

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.

 

[T. Valko]

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)

 

[Roger Govier]

Hi

No need for any formulae to achieve this.
Place your cursor in the cell where you want your results to begin.
Data>Consolidate>select range of your data A2:B10>tick Use labels in Left
Column>OK

 

[Guest]

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:$B1)-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

 

[Guest]

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:$B1&"")-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

 

[Guest]

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

 

[Harlan Grove]

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:$B1&"")-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.

 

[Guest]

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

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:$B1&"")-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]

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,MATCH(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

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:$B1&"")-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.

 

[Harlan Grove]

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

 

[Harlan Grove]

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.

 

[Guest]

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

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

XL2003, WinXP

 

[Guest]

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.

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,MATCH(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

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:$B1&"")-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.

 

[Guest]

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!

 

[Guest]

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.

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

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:$B1&"")-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.

 

[Guest]

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

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

XL2003, WinXP

 

[Guest]

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.

 

[Guest]

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

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.

 

[Harlan Grove]

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

 

[Guest]

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.