VBA for finding duplicated rows of data with changes

[Abgar]

Hi all,

Hoping someone can help with a quick VBA code (or point me in the right direction).

I have a data worksheet, which contains many rows of data, spreading across multiple columns. Each day, a new extract of data is added to the spreadsheet (added as new rows at the bottom), and there will be duplicated data.
We have been using conditional formatting on Column A (a unique ID number) to show the duplicates, and then we delete the duplicates.
When adding the new data, sometimes the Column A will be the same, but there will be a different value in Column H (based on changes in the system we extract the data from).

I'm hoping to essentially have a VBA code that will run through each row and check if ALL data for the row in columns A-H is the same. If it is all the same, highlight the duplicated row of data in Fill Colour RED
If Column A is the same, but there is any difference in data from Column B-H, then highlight the row in Fill Colour Green.
And if Column A is unique (not duplicated), then don't highlight that row at all.

Per the below example, the top 8 rows are the original data, then the new data is added below. Rows 11, 13 and 15 are highlighted green, becuase the ID in column A is a duplicate, but some of the other data is different.
Rows 12 and 14 are highlighted red becuase Column A is a duplicate, but all the other data is the same.
Columns 16-18 are not highlighted, becuase Column A is a new unique value, and is not duplicated.

Book1
	A	B	C	D	E	F	G	H
1	Unique ID	Surname	First name	Client	State	Category	Contact	Date
2	302383	Slater	Tom	Bills Boats	Tasmania		Lockyer	17/03/2021
3	297842	Smith	Peter	Bills Boats	Queensland		Lockyer	17/03/2021
4	302097	Temple	Paul	Toms Tomatos	New South Wales	Biggest	Lockyer	17/03/2021
5	302272	Rough	Mark	Betteys Beer	Tasmania		Trident	17/03/2021
6	299573	Grout	John	Bills Boats	Tasmania	Baddest	Trident	17/03/2021
7	302900	Wilson	Luke	Toms Tomatos	Queensland		Goodall	17/03/2021
8	301106	Smith	Matthew	Bills Boats	New South Wales		Peters	17/03/2021
9	302163	Bleeker	Sally	Betteys Beer	Tasmania		Smith	17/03/2021
10
11	302383	Slater	Tom	Bills Boats	Tasmania		Lockyer	18/03/2021
12	297842	Smith	Peter	Bills Boats	Queensland		Lockyer	17/03/2021
13	302097	Temple	Paul	Toms Tomatos	New South Wales	Biggest	Smith	17/03/2021
14	302272	Rough	Mark	Betteys Beer	Tasmania		Trident	17/03/2021
15	299573	Grout	John	Toms Tomatos	Tasmania	Baddest	Trident	17/03/2021
16	123456	Wilson	Luke	Toms Tomatos	Queensland		Goodall	17/03/2021
17	654321	Smith	Matthew	Bills Boats	New South Wales		Peters	17/03/2021
18	987645	Bleeker	Sally	Betteys Beer	Tasmania		Smith	17/03/2021
Sheet1

Thanks in advance for any info you can share.

Cheers,
ABGar

 

[Zot]

I loop from last row so that I would mark the duplicate or almost similar at later rows and not in the early rows. It works for this small sample

Sub MarkDupe()

Dim n As Long, m As Long, k As Long, eRow As Long
Dim ArryData(), Element As Variant

eRow = Range("A" & Rows.Count).End(xlUp).Row

For n = eRow To 2 Step -1
    For m = eRow To 2 Step -1
        If Range("A" & m).Interior.ColorIndex = xlColorIndexNone And Not n = m Then
            If Range("A" & m) = Range("A" & n) Then
                For k = 2 To 8
                    If Not Cells(m, k) = Cells(n, k) Then
                        Range("A" & n, "H" & n).Interior.ColorIndex = 10
                        GoTo NotAll
                    End If
                Next
NotAll:
                If Range("A" & n).Interior.ColorIndex = xlColorIndexNone Then
                Range("A" & n, "H" & n).Interior.ColorIndex = 3
                End If
            End If
        End If
    Next
Next

End Sub

 

[Abgar]

Elegant and Simple - Thanks Zot Works a charm.

 

[Zot]

Elegant and Simple - Thanks Zot Works a charm.

Thanks for update

 

[Abgar]

@Zot - sorry, when processing on the live data (much larger data range), we realised that some of the data in columns E and F will not match, (this is due to our systems) whereas all other data does match.
Is it possible to amend the script to exclude comparing columns E and F, but still compare A-D and G-H?

 

[Zot]

@Zot - sorry, when processing on the live data (much larger data range), we realised that some of the data in columns E and F will not match, (this is due to our systems) whereas all other data does match.
Is it possible to amend the script to exclude comparing columns E and F, but still compare A-D and G-H?

The column correspond to variable k in the code. B = 2 (second column) to H = 8 (eight column). Now need to skip 5 and 6.

Instead of using IF k= 5 0r 6, I use array to define value of k. You can modify the Arryk for column to be compared.

Sub MarkDupe()

Dim n As Long, m As Long, k As Long, eRow As Long
Dim ArryData(), Arryk(), Element As Variant

eRow = Range("A" & Rows.Count).End(xlUp).Row
Arryk = Array(2, 3, 4, 7, 8)

For n = eRow To 2 Step -1
    For m = eRow To 2 Step -1
        If Range("A" & m).Interior.ColorIndex = xlColorIndexNone And Not n = m Then
            If Range("A" & m) = Range("A" & n) Then
                For k = 0 To 4
                    If Not Cells(m, Arryk(k)) = Cells(n, Arryk(k)) Then
                        Range("A" & n, "H" & n).Interior.ColorIndex = 10
                        GoTo NotAll
                    End If
                Next
NotAll:
                If Range("A" & n).Interior.ColorIndex = xlColorIndexNone Then
                Range("A" & n, "H" & n).Interior.ColorIndex = 3
                End If
            End If
        End If
    Next
Next

End Sub

 

[Abgar]

Thanks Zot - that works perfectly now

Can you just confirm for me, this line:
For k = 0 To 4

Is that now referencing the columns listed in the array? i.e. k0 = array column 2, and k4 = array column 8?

 

[Zot]

Thanks Zot - that works perfectly now

Can you just confirm for me, this line:
For k = 0 To 4

Is that now referencing the columns listed in the array? i.e. k0 = array column 2, and k4 = array column 8?

k is not the index number for array content where array starts from Arryk(0), Arryk(1), Arryk(2), Arryk(3) and Arryk(4) correspond to columns 2, 3, 4, 7, 8 in this code. Array by default starts with 0 unless you have Option Base 1 in declaration.

 

[Abgar]

G

k is not the index number for array content where array starts from Arryk(0), Arryk(1), Arryk(2), Arryk(3) and Arryk(4) correspond to columns 2, 3, 4, 7, 8 in this code. Array by default starts with 0 unless you have Option Base 1 in declaration.

Great, thanks so much for the helpful education. Really appreciate your time/efforts to this for me.

Have a wonderful day.

 

[Zot]

G

Great, thanks so much for the helpful education. Really appreciate your time/efforts to this for me.

Have a wonderful day.

You too. Right now I'm stuck on my own code too. ?

Thanks for update