URGENT!!! I am trying to elimate duplicates on my excel spreadsheet!?
I don't want there to be duplicates. I am trying =if(c2=c3, then 'dup') but I keep getting an error. What am I doing wrong???!!!?
I don't want there to be duplicates. I am trying =if(c2=c3, then 'dup') but I keep getting an error. What am I doing wrong???!!!?
introversive_guy
Favorite Answer
=IF(C2=C3, "dup")
voyager
Do an advanced filter
=========>
Select the area
Go to Data
Filter
Advanced Filter
Select "Unique records only"
Copy the resulting area to a new sheet
======================>
If you want to do it using the above formula you have tried, first you have to sort the list and type in C3
=IF(C2=C3,"DUPLICATE","")
Copy it down
Convert that formula column to values (copy -paste special - values)
Select the list
Go to Data
Filter -Auto filter
Select "DUPLICATE" from that column
Delete (delete rows)
AQuestionMark
use the following formula in condition formatting a cell which might have duplicate with other cells above it in the same column
=AND(MATCH(A10, $A$1: INDIRECT(ADDRESS(ROW()-1, COLUMN() ,4)),0)>0 ,A10<>"")
Say in A10, go to menu format conditional formatting, choose "formula is" and put the above formula in, click the format.. button to do your color format
use format painter to copy the format from A10 to range A1 to A11 or the whole column, then whenever, there is a duplicate appear in range, it will be colored as you formatted it.
Note: A10 in the formula is the cell you put this conditional formatting formula in, $A$1 is the very first cell of the block range where you want to see if A10 is a duplicate, and it has to has $ signs in the address. If you apply the formula to the column, it is normal that A1 will has color showed even if all the value in column is unique.
In practice, after conditional formatting is added to column A, if last refresh you got 1,2,3 in A1:A3 and current refresh you added A4:A7 1,2,3,4 then A4:A6 will be colored as they duplicate A1:A3 but not A7 which is unique in the column, the old refresh records and the new unique refreshed records will be separated by a color zone A4:A6
if you can see the above code or formula clearly download this demo http://www.freewebs .com/swhtam/ YA/refresh.xls
Please note that i put 2 space in the link above to get around the 999 page error, so take the space out to use the link thanks