Duplicate Values Over Three+ Columns Using VBA

[KatieA]

Good Morning Everyone, Happy Christmas and All.

Please does anybody know who to adapt this code in order for it to look over duplicate information over three or columns rather than just the one. So if the information was in column A, B & C, and I would say want the answer "duplicate" to appear in column D?

Please Login or Register  to view this content.

[CheeseSandwich]

Hi Katie,

Will the duplicates always be on the same row between columns, for example are you checking row columns A, B & C for them all to match?

Or

Are you looking to see if a value in say cell A2 appears anywhere in column A, B & C to denote duplicate?

[KatieA]

Good Morning Cheese Sandwich,

I am looking for the data to match in all at least 5 columns A,B,C,D,E and then declare as duplicate, I have set up a basic example to show you. I would like to show duplicates for the same vehicle, on the same date, customer, postcode and country. I am not sure how to get the code to do this though.

[bebo021999]

Option Explicit 
Sub test() 
Dim Lr& 
Dim cell As Range 
Lr = Cells(Rows.Count, "A").End(xlUp).Row 
For Each cell In Range("A1:A" & Lr) 
    If Application.WorksheetFunction.CountIf(Range("A1:C" & Lr), cell) > 1 Then 
    cell.Offset(, 3).Value = "Duplicate" 
    End If 
Next 
End Sub  

[Glenn Kennedy]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code (first post in thread).

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[KatieA]

Glenn sorry I didnt do this right, I have adjusted it.

[bebo021999]

Try again

Option Explicit 
Sub test() 
Dim Lr& 
Dim cell As Range 
Dim dic As Object 
Set dic = CreateObject("Scripting.dictionary") 
Lr = Cells(Rows.Count, "A").End(xlUp).Row 
For Each cell In Range("A1:A" & Lr) 
    If Not dic.exists(cell.Value & cell.Offset(0, 1).Value & cell.Offset(0, 2).Value & cell.Offset(0, 3).Value & cell.Offset(0, 4).Value) Then 
        dic.Add cell.Value & cell.Offset(0, 1).Value & cell.Offset(0, 2).Value & cell.Offset(0, 3).Value & cell.Offset(0, 4).Value, 1 
    Else 
        cell.Offset(, 5).Value = "Duplicated" 
    End If 
Next 
End Sub  

[KatieA]

Bebo021999, thank you ever so much for showing me how to do this. I have been racking my brain with just normal excel formula. This is exactly what I was looking for, and what I wanted to achieve. This will help me so much now. I am over the moon.