look for duplicates in the same column loop

[user125]

Hello,

This might be a very simple solution but I just can't seem to get my head around it. Maybe one of you experts could help me out!

I am looking to create a loop where the code looks for duplicates in one column (in this case, column A) and if it is a duplicate, the cell next to it would say "duplicate" ("B4" and"B5"). I need to create a loop as there could be multiple cells to look for duplicates.

If you need more clarification, I would be more than happy to provide more information. Thanks in advance!

 

[Fluff]

Put this in B1 & copy down
=IF(COUNTIF(A$1:A1,A1)>1,"Duplicate","")

 

[user125]

Hello @Fluff ,

I was actually looking for VBA code that does this and not a formula. Would you be able to help me with the VBA code?

Thanks!

 

[Fluff]

Ok, how about

Sub user125()
   With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .Formula = "=if(countif(A$1:A1,A1)>1,""Duplicate"","""")"
      .Value = .Value
   End With
End Sub

 

[user125]

Ok, how about

Sub user125()
   With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .Formula = "=if(countif(A$1:A1,A1)>1,""Duplicate"","""")"
      .Value = .Value
   End With
End Sub

Thanks Fluff. That worked perfectly!

 

[Fluff]

You're welcome & thanks for the feedback