List of unique elements
Elliott Marchand
I've been racking my brain trying to come up with a way to
get a simple list of unique values. The problem is this:
in column A I have a list text data. In column B I want a
list of the unique elements from column A.
The best I can come up with is to have the cell in column
B show a value when the corresponding cell in A hasn't
been listed yet. Naturally, this leaves a lot of blanks
in column B.
It seems like this would be a simple thing to do.
Elliott
Ron de Bruin
http://www.contextures.com/xladvfilter01.html#FilterUR
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"Elliott Marchand" <emar...@csc.com> wrote in message news:01c701c36bf2$752ac220$a301...@phx.gbl...
Karen
you insist on using a formula, try this:
If you have a range of data as follows:
apples
oranges
oranges
oranges
apples
apples
cherries
berries
cherries
and you want to pull a list out which names each of the
unique values (i.e. apples, oranges, cherries, berries),
here’s what to do.
1: Name your data range (highlight it, go to the pulldown
menu Insert, Name, Define….) ‘data’.
2: Pick any cell outside your data range to enter in the
following equation.
3: Array-enter this formula (press Ctrl-Shift-Enter to
enter it instead of just Enter)
{=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW($A$1:INDIRECT
(CHAR(65)&COUNTA(data))),MATCH(data,data,0),""),ROW()-ROW
(data)+1))}
[don’t type the brackets { }; they indicate an array-
entered formula]
4: Fill it down the column; it will return #NUM when it
runs out of unique entries.
Hope that helps,
Karen
>.
>
2rrs
=IF(COUNTIF($A$3:A3,A3)=1,A3,"") (copy down)
This will return the unique items and leave blank cells for the dupl
cells.
You can now auto filter on (Blanks)and delete the blanks; this will
leave a list of unique values.
But as Ron and Karen pointed out, anvanced filter is 1st choice.
"Elliott Marchand" <emar...@csc.com> wrote in message news:<01c701c36bf2$752ac220$a301...@phx.gbl>...
Leo Heuser
Here's another array formula solution:
Assuming list in B8:B16 enter the formula in
e.g. D8. D7 must be empty or else must not
hold data present in B8:B16
=INDEX($B$8:$B$16,MIN(IF(COUNTIF($D$7:D7,$B$8:$B$16)=0,
ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
The formula must be entered with <Shift><Ctrl><Enter>, also if
edited later. If done correctly, Excel will display the formula in
the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.
Copy D8 down with the fill handle (the little square in
the lower right corner of the cell)
When no more unique values exist, the cell will contain the
#REF! error.
--
Best Regards
Leo Heuser
MVP Excel
Followup to newsgroup only please.
"Elliott Marchand" <emar...@csc.com> skrev i en meddelelse
news:01c701c36bf2$752ac220$a301...@phx.gbl...
Elliott Marchand
This is almost working for me. The last little hiccup is
that I have blanks in my column A. I've fiddled with this
formula for quite a while and I can't get it to ignore the
blanks. I end up with "0" as my entry in column B.
>.
>
Harlan Grove
>This is almost working for me. The last little hiccup is
>that I have blanks in my column A. I've fiddled with this
>formula for quite a while and I can't get it to ignore the
>blanks. I end up with "0" as my entry in column B.
>
>>-----Original Message-----
>>=INDEX($B$8:$B$16,MIN(IF(COUNTIF($D$7:D7,$B$8:$B$16)=0,
>>ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
If you don't want to include blanks as legitimate distinct entries, try the
following array formulas.
D7 (topmost result):
=INDEX($B$8:$B$16,MATCH(0,--ISBLANK($B$8:$B$16),0))
D8 (next result):
=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(B$1:B1,$B$8:$B$16),0))
Fill D8 down as far as needed. The latter formula will return #N/A when there
are no more distinct entries in $B$8:$B$16.
--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Leo Heuser
Here's the version for blanks:
=INDEX($B$8:$B$16,MIN(IF((COUNTIF($D$7:D7,$B$8:$B$16)=0)*
($B$8:$B$16<>""),ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
--
Best Regards
Leo Heuser
MVP Excel
Followup to newsgroup only please.
"Elliott Marchand" <emar...@csc.com> skrev i en meddelelse
news:06ef01c36cde$03323f30$a101...@phx.gbl...
Harlan Grove
>Here's the version for blanks:
>
>=INDEX($B$8:$B$16,MIN(IF((COUNTIF($D$7:D7,$B$8:$B$16)=0)*
>($B$8:$B$16<>""),ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
Maybe picky, maybe not. This formula fails to distinguish between truly blank
cells and cells evaluating to zero-length strings. There are times when the two
should not be treated the same.
Leo Heuser
"Harlan Grove" <hrl...@aol.com> skrev i en meddelelse
news:99t3b.17757$cJ5....@www.newsranger.com...
> "Leo Heuser" wrote...
> >Here's the version for blanks:
> >
> >=INDEX($B$8:$B$16,MIN(IF((COUNTIF($D$7:D7,$B$8:$B$16)=0)*
> >($B$8:$B$16<>""),ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
> ..
>
> Maybe picky, maybe not. This formula fails to distinguish between truly
blank
> cells and cells evaluating to zero-length strings. There are times when
the two
> should not be treated the same.
I felt, that a destinction was not relevant here, but I might have been
wrong.
In that case, this array formula will do:
=INDEX($B$8:$B$16,MIN(IF((COUNTIF($D$7:D7,$B$8:$B$16)=0)*
NOT(ISBLANK($B$8:$B$16)),ROW($B$8:$B$16)-ROW($B$8)+1,ROWS($B$8:$B$16)+1)))
In this situation D7 must not be empty. It has to contain data not
present in B8:B16.
LeoH
Leo Heuser
"Harlan Grove" <hrl...@aol.com> skrev i en meddelelse
news:Mgb3b.17601$cJ5....@www.newsranger.com...
>
> If you don't want to include blanks as legitimate distinct entries, try
the
> following array formulas.
>
> D7 (topmost result):
> =INDEX($B$8:$B$16,MATCH(0,--ISBLANK($B$8:$B$16),0))
>
> D8 (next result):
>
=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(B$1:B1,$B$8:$B$16),0))
>
> Fill D8 down as far as needed. The latter formula will return #N/A when
there
> are no more distinct entries in $B$8:$B$16.
>
apples
oranges
apples
berries
apples
apples
cherries
berries
cherries
Your formulae entered in D7, D8 and down return:
apples
apples
apples
apples
apples
apples
apples
apples
oranges
berries
berries
cherries
cherries
cherries
#N/A
!?
LeoH
Harlan Grove
If only true blanks should be excluded, the following array formulas require
fewer function calls.
D7 (topmost result):
=INDEX($B$8:$B$16,MATCH(0,--ISBLANK($B$8:$B$16),0))
D8 (next result):
=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(D$7:D7,$B$8:$B$16),0))
If both true blanks and zero-length strings should be excluded, the following
alternative array formulas require even fewer function calls.
D7 (topmost result):
=INDEX($B$8:$B$16,MATCH(0,--($B$8:$B$16=""),0))
D8 (next result):
=INDEX($B$8:$B$16,MATCH(0,($B$8:$B$16="")+COUNTIF(D$7:D7,$B$8:$B$16),0))
Harlan Grove
>"Harlan Grove" <hrl...@aol.com> skrev i en meddelelse
>news:Mgb3b.17601$cJ5....@www.newsranger.com...
>>If you don't want to include blanks as legitimate distinct entries, try
>>the following array formulas.
>>
>>D7 (topmost result):
>>=INDEX($B$8:$B$16,MATCH(0,--ISBLANK($B$8:$B$16),0))
>>
>>D8 (next result):
>>=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(B$1:B1,$B$8:$B$16),0))
>If B8:B16 contains
..
>!?
I screwed up. Didn't change all references from those I had used in my test
workbook to ones matching up with the ranges you were using. Change the second
formula to
=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(D$7:D7,$B$8:$B$16),0))
and fill down until it gives #N/A.
Leo Heuser
require
> fewer function calls.
>
> D7 (topmost result):
> =INDEX($B$8:$B$16,MATCH(0,--ISBLANK($B$8:$B$16),0))
>
> D8 (next result):
>
=INDEX($B$8:$B$16,MATCH(0,ISBLANK($B$8:$B$16)+COUNTIF(D$7:D7,$B$8:$B$16),0))
>
> If both true blanks and zero-length strings should be excluded, the
following
> alternative array formulas require even fewer function calls.
>
> D7 (topmost result):
> =INDEX($B$8:$B$16,MATCH(0,--($B$8:$B$16=""),0))
>
> D8 (next result):
> =INDEX($B$8:$B$16,MATCH(0,($B$8:$B$16="")+COUNTIF(D$7:D7,$B$8:$B$16),0))
Yeah, I know mine is bigger than yours <g>.
LeoH
Harlan Grove
..
>Yeah, I know mine is bigger than yours <g>.
Nose, stomach, butt, feet or other?
Leo Heuser
> "Leo Heuser" wrote...
> ..
> >Yeah, I know mine is bigger than yours <g>.
>
> Nose, stomach, butt, feet or other?
Formula actually. The <g> was kicked in to add a bit of fun
to the remark.
LeoH
Bill Mathews
you want to extract unique values from _must_ be values, not formulas. Took
me a bit of time to figure this out.
Cheers,
Bill
"Karen" <karen.fe...@vh1staff.com> wrote in message
news:028701c36bfa$709317f0$a301...@phx.gbl...