Retrieving Unique Values From A List - TIP

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

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Use this in cell B2, and copy down:

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


--
HTH,
Bernie
MS Excel MVP


"ddwebb" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>