Find Duplicates across 13 Columns

[mrsbrannon]

Hello all,

I am trying to find a way to locate exact duplicates across 13 columns of data. The worksheet that I'm using as a template has a VBA written that looks like the below:

' Final check is to see if there are duplicate rows. This is just informational. If a duplicate exists then the duplicate rows are colored yelow
Set duplicateRows = CreateObject("Scripting.Dictionary")
For x = 22 To rowData
dupRow = wsData.Range("B" & x).Value & wsData.Range("C" & x).Value & wsData.Range("D" & x).Value & wsData.Range("D" & x).Value & wsData.Range("E" & x).Value & _
wsData.Range("F" & x).Value & wsData.Range("G" & x).Value & wsData.Range("H" & x).Value & wsData.Range("I" & x).Value & wsData.Range("J" & x).Value & _
wsData.Range("K" & x).Value & wsData.Range("L" & x).Value

If Not duplicateRows.exists(dupRow) Then
duplicateRows.Add dupRow, x
Else
wsData.Range("B" & x & ":N" & x).Interior.ColorIndex = 36
wsData.Range("B" & duplicateRows(dupRow) & ":N" & duplicateRows(dupRow)).Interior.ColorIndex = 36
End If

Next x

I'm trying to figure out what changes I need to make to have this apply to my worksheet. I have attached an example.

Thank you!

CROSS-POSTED - https://www.mrexcel.com/forum/excel-...3-columns.html

[AlphaFrog]

Here's an alternate method using conditional formatting.

Highlight Duplicate Records

[mrsbrannon]

Here's an alternate method using conditional formatting.

Highlight Duplicate Records

Will this work for large amounts of data? I may have up to 100 rows of data to compare and need exact matches only across the 13 columns.

[xladept]

This will work for 14 Columns:

Please Login or Register  to view this content.

[mrsbrannon]

Tried copying and pasting onto my worksheet. Didn't work.

[xladept]

Hi Mrs Brannon,

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

[mrsbrannon]

"Run-time error '13'
Type mismatch

[xladept]

Does your data start in row 2? Do you have a reference to Microsoft Scripting Runtime? Which line was highlighted?

[mrsbrannon]

Yes, row 2. No idea on the Microsoft Scripting Runtime and no lines were highlighted.

[xladept]

In the VBE (that's the window where you can see the code) Click on Tools - References - Microsoft Scripting Runtime.

If that doesn't fix it, I'll need to see a sample where it fails

[mrsbrannon]

Okay, thank you. I did it but I still don't see it working. I'm going to try a couple of things and I'll let you know if I can't figure something out. Thanks!