I received an email yesterday regarding the above topic and I have a
spreadsheet that has hundreds of names (i.e. Doe, J) and I thought that
this would be a cool way to see what distinct names are listed within
the column of names.
I entered the following into the
=INDEX($A$1:$A$150,MATCH(0,COUNTIF($B$1:B1,$A$1:$A $150),0)) in field
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 the
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


--
ddwebb
------------------------------------------------------------------------
ddwebb's Profile: http://www.excelforum.com/member.php...fo&userid=9057
View this thread: http://www.excelforum.com/showthread...hreadid=399145

ddwebb Wrote:
I received an email yesterday regarding the above topic and I have a
spreadsheet that has hundreds of names (i.e. Doe, J) and I thought that
this would be a cool way to see what distinct names are listed within
the column of names.
I entered the following into the
=INDEX($A$1:$A$150,MATCH(0,COUNTIF($B$1:B1,$A$1:$A $150),0)) in field
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 the
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")


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=399145

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 than
once.
We want to create a list in column B in which each value from column A
may only appear once.

Solution:

Use the INDEX, MATCH, and COUNTIF functions as shown in the following
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 until
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 not
work for me. Using Excel 2002.
dw


--
ddwebb
------------------------------------------------------------------------
ddwebb's Profile: http://www.excelforum.com/member.php...fo&userid=9057
View this thread: http://www.excelforum.com/showthread...hreadid=399145

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" wrote in message
...

I received an email yesterday regarding the above topic and I have a
spreadsheet that has hundreds of names (i.e. Doe, J) and I thought that
this would be a cool way to see what distinct names are listed within
the column of names.
I entered the following into the
=INDEX($A$1:$A$150,MATCH(0,COUNTIF($B$1:B1,$A$1:$A $150),0)) in field
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 the
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


--
ddwebb
------------------------------------------------------------------------
ddwebb's Profile: http://www.excelforum.com/member.php...fo&userid=9057
View this thread: http://www.excelforum.com/showthread...hreadid=399145

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


--
ddwebb
------------------------------------------------------------------------
ddwebb's Profile: http://www.excelforum.com/member.php...fo&userid=9057
View this thread: http://www.excelforum.com/showthread...hreadid=399145

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" wrote in message
...

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 than
once.
We want to create a list in column B in which each value from column A
may only appear once.

Solution:

Use the INDEX, MATCH, and COUNTIF functions as shown in the following
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 until
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 not
work for me. Using Excel 2002.
dw


--
ddwebb
------------------------------------------------------------------------
ddwebb's Profile: http://www.excelforum.com/member.php...fo&userid=9057
View this thread: http://www.excelforum.com/showthread...hreadid=399145