List of unique elements

E

Elliott Marchand

All -

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
 
K

Karen

I'm with Ron - I'm all about the Advanced Filter. But if
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
 
2

2rrs

You could also use this formula:
=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.
 
E

Elliott Marchand

Leo -

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

Leo Heuser

Elliott

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

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)))
Click to expand...
...

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

Leo Heuser

Harlan Grove said:
...
..

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.
Click to expand...

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
 
L

Leo Heuser

Harlan Grove said:
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.
Click to expand...
If B8:B16 contains

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
 
H

Harlan Grove

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.
Click to expand...

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

Harlan Grove

...
If B8:B16 contains ...
!?
Click to expand...

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

Bill Mathews

N.B. - For the advanced filter to work, the cells that hold the data that
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 said:
I'm with Ron - I'm all about the Advanced Filter. But if
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
-----Original Message-----
All -

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
.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Merging from an Excel file with graphics 1
Automation of scanned file storage based on content 0
Trying to count based on multiple criteria & cell comparisons 4
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Need a proper guru! My head hurts ;( 1
Counting unique values in multiple columns 4
Problem using IF statement with dates as part of the formula 4
FINDING AND LISTING UNIQUE DATA IN A COLUMN 3

Top