find Unique values (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Modify the formula in my other reply to:


=IF(COUNTIF($A$1:$A$6000,A22)>1,"","Unique")


And follow the same instructions.

Reply | Quote

Thank you

Reply | Quote

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.

Reply | Quote

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:
=COUNTIF($A$2:$A$6130,A2)=1
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.

Reply | Quote

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


=IF(COUNTIF($A$1:$A$6000,A22)>1,"Duplicate","")


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.

Reply | Quote