Finding Duplicate Cell Entries

[John]

Is there a simple macro/VBA for checking a specific column in which there are
random duplicate text entries? I do not want to delete, remove or move them.
For example my Column C contains about 2000 song title entries some of which
are duplicates and I need to identify and tag them. I only need to ID them
for now. The simpler the better.
Thanks

 

[Mike H]

Hi,

You can do that with a formula. Put this in an empty column and drag down.
It will show TRUE if a title is duplicated. Alter the column C range to suit

=COUNTIF($C$1:$C$20,C1)>1

Mike

 

[Ms-Exl-Learner]

Paste this formula in C1 cell.
=IF(COUNTIF($C$1:$C1,$C1)=1,"NO
DUPLICATION",IF(COUNTIF($C$1:$C1,$C1)>1,"DUPLICATE",""))

Copy the C1 cell and paste it for the remaining 2000 cells of C Column.

Remember to Click Yes, if this post helps!

 

[Pete_UK]

Put this formula in D2:

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)=1,"First","Duplicate"))

then copy down as far as you like. This assumes you have a header row.
You can apply a filter to column D to select First (i.e. the first
occurrence) or Duplicate.

If you want to class the first occurrence (of many) as a duplicate
also, then you could do this:

=IF(C2="","",IF(COUNTIF(C:C,C2)>1,"Duplicate","Unique"))

Hope this helps.

Pete

 

[Stefi]

One way: this formula in D2 and filled down to the last line returns an empty
string if title in column C in the corresponding row is not duplicated, but
returns the row No. of duplicated title if there is one:

=IF(ISERROR(MATCH(C2,C3:$C$2000,0)),"",ROW()+MATCH(C2,C3:$C$2000,0))

Adjust 2000 to the really last row No.
--
Regards!
Stefi



„John†ezt írta:

 

[Ms-Exl-Learner]

A small correction in my below post.

Paste this formula in D1 cell.
=IF(COUNTIF($C$1:$C1,$C1)=1,"NO
DUPLICATION",IF(COUNTIF($C$1:$C1,$C1)>1,"DUPLICATE",""))

Copy the D1 cell and paste it for the remaining 2000 cells of D Column.
Apply the Autofilter in D Column and select Duplicate to view the duplicate
records.

Remember to Click Yes, if this post helps!

 

[מיכ×ל (מיקי) ×בידן]

* Select all(!) 2000 cells.
* Declare "Conditional Format" > as per "Formula" >
=COUNTIF($C$1:C1,C1)>1
* declare some background color i order to identify the duplicate names.
*** This method will color from the second name and will not touch the first
one.
Micky