Retrieving Unique Values From A List - TIP

[ddwebb]

I received an email yesterday regarding the above topic and I have
spreadsheet that has hundreds of names (i.e. Doe, J) and I thought tha
this would be a cool way to see what distinct names are listed withi
the column of names.
I entered the following into th
=INDEX($A$1:$A$150,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$150),0)) in fiel
B2.
Column A has a sample of names from A1 to A150
Column B, Row 1 is blank
Column B, Row 2 has this function. I copied the formula down to B150
of course when I do so, the formula changes ($B$1:B2, ...). All th
fields in the Column B have the #N/A value.
I know that there are duplicate rows of data in Column A.
So what is wrong?

dw :confused

 

[Paul Sheppard]

I received an email yesterday regarding the above topic and I have
spreadsheet that has hundreds of names (i.e. Doe, J) and I thought tha
this would be a cool way to see what distinct names are listed withi
the column of names.
I entered the following into th
=INDEX($A$1:$A$150,MATCH(0,COUNTIF($B$1:B1,$A$1:$A$150),0)) in fiel
B2.
Column A has a sample of names from A1 to A150
Column B, Row 1 is blank
Column B, Row 2 has this function. I copied the formula down to B150
of course when I do so, the formula changes ($B$1:B2, ...). All th
fields in the Column B have the #N/A value.
I know that there are duplicate rows of data in Column A.
So what is wrong?

dw

Hi ddwebb

Try this

=IF(COUNTIF($A$1:$A$10,B1)=0,"No Match","Match"

 

[ddwebb]

I tried that and that did not work.

What the Excel Tip e-mail was:
Problem:
Column A contains a list of values, each of which may appear more tha
once.
We want to create a list in column B in which each value from column
may only appear once.

Solution:

Use the INDEX, MATCH, and COUNTIF functions as shown in the followin
Array formula:
{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$2:B2,$A$2:$A$8),0))}

Note:
The first cell in the unique list (column B) must remain empty.
The formula should be entered in the second cell and copied down unti
the #N/A error is returned.


List_________Distinct List
Red
Blue_________Red
Green________Blue
Yellow_______Green
Green________Yellow
Blue_________#N/A
Blue

I have tried this in a separate worksheet to experiment and it does no
work for me. Using Excel 2002.
dw :confused

 

[Bernie Deitrick]

dw,

In cell B2, use the formula
=IF(COUNTIF($A$1:A2,A2)=1,"Unique","Duplicate")
and copy down.

The second, third, etc. instances of dupes will have "Duplicate".

IF you want the FIRST instance of dupes to also have "Duplicate", then use:

=IF(COUNTIF($A$1:$A$150,A2)=1,"Unique","Duplicate")

HTH,
Bernie
MS Excel MVP

 

[ddwebb]

I wanted a little more than Duplicate or unique. The original formula ws
to return the distinct values or duplicate values from column A.

I finally got it to work. what I did not know was that after entering
the original formula:
=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$2:B4,$A$2:$A$8),0))
I was to press the SHIFT+CTRL+ENTER keys to make this an array and then
copy the formula down to B150.
This now shows all the Unique values in Column A displayed in Column
B.
Just one tiny step that the ExcelTip e-mail did not provide and I did
not know.
Thanks
dw

 

[Bernie Deitrick]

Use this in cell B2, and copy down:

=IF(COUNTIF($A$1:A2,A2)=1,A2,"")