# Thread: find Unique values (Excel 2003)

1. ## Re: find Unique values (Excel 2003)

attached is my list which is in column A.
I merged two lists into one and wanted to advance filter the large list and find any values that are unique and not duplicated

2. ## find Unique values (Excel 2003)

I have a problem with a list that is 1 col by 6000 rows
the problem is there is unique values (30 to be exact) in the list and I want to find them
Any one know a way to do this

3. ## Re: find Unique values (Excel 2003)

A quick and dirty way is to put the list in ascending order and in a new column place:

=if(A2=A1,"Duplicate","")

Filldown the column and you can then use an autofilter to list the duplicates

4. ## Re: find Unique values (Excel 2003)

My apologies, I am looking for the unique values and not the duplicates. I changed my original post but you are very quick and thank you for that

5. ## Re: find Unique values (Excel 2003)

If the data is in column A, enter the following formula in cell B1.

<code>
=IF(COUNTIF(\$A\$1:\$A\$6000,A22)>1,"Duplicate","")
</code>

Now select cell B1 and double click on the fill handle in the lower right corner. This should fill the formula down as far as the data in column A and give you the word "Duplicate" next to all of the duplicates.

6. ## Re: find Unique values (Excel 2003)

Modify the formula in my other reply to:

<code>
=IF(COUNTIF(\$A\$1:\$A\$6000,A22)>1,"","Unique")
</code>

Thank you

8. ## Re: find Unique values (Excel 2003)

I don't know if you found your answer but I wanted to point you to another thread, posting # 411,338, that may provide what you seek (if you have not already found it.

9. ## Re: find Unique values (Excel 2003)

Just for information, you could also do this using the advanced filter. Insert a header row above your data and enter a header in A1 (let's say 'Value'). Then enter a criteria header (let's say 'Unique') in D1 and in D2 enter this formula:
<code>=COUNTIF(\$A\$2:\$A\$6130,A2)=1</code>
Then choose Data->Filter->Advanced Filter form the menu, select \$A\$1:\$A\$6130 as the List Range, and select \$D\$1:\$D\$2 as the criteria range and click OK.
FWIW.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•