Macro to search duplicates by matching any 3 of the 5 fields in a record.

Abhijeet_Chib · Feb 27, 2014

Hello

I have a database with a lot of records and each record has 5 fields namely INVOICE NUMBER, DATE, AMOUNT, SHIPMENT NUMBER and QUANTITY.

How do i write a Macro that searches for duplicates. The way to searching duplicates is that if any three fields of the record match then the record is highlighted in a separate colour. The matching fields are not specified, they can be any three fields.

For example, if INVOICE NUMBER, SHIPMENT NUMBER and QUANTITY match in some records then its shown as duplicates and highlighted. Similarly if INVOICE NUMBER, QUANTITY and DATE matches in a few records then they are listed as duplicates as well.

So in the database, out of the 5 fields if any 3 match then they are listed as duplicates and highlighted in a different colour.

Can anyone please help me write a Macro that does that.
Cheers !!
Abhii

Worf · Mar 2, 2014

Hello Abhii and welcome to the Board

If your database looks like this:

Plan1

*	A	B	C	D	E

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:112px;"><col style="width:85px;"><col style="width:76px;"><col style="width:128px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Invoice Number[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Shipment Number[/TD]
[TD="align: center"]Quantity[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]1W6[/TD]
[TD="align: center"]25/07/2012[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]6T4[/TD]
[TD="align: center"]07/11/2013[/TD]
[TD="align: center"]700[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]120[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]9G5[/TD]
[TD="align: center"]20/02/2015[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]30[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]9T6[/TD]
[TD="align: center"]04/04/2014[/TD]
[TD="align: center"]1100[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: center"]6T4[/TD]
[TD="align: center"]17/09/2017[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: center"]8H7[/TD]
[TD="align: center"]31/12/2018[/TD]
[TD="align: center"]1500[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]70[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: center"]8H7[/TD]
[TD="align: center"]14/04/2020[/TD]
[TD="align: center"]1700[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]70[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]9G5[/TD]
[TD="align: center"]04/04/2014[/TD]
[TD="align: center"]1900[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]40[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="align: center"]6T4[/TD]
[TD="align: center"]10/11/2022[/TD]
[TD="align: center"]1300[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]50[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]
[TD="align: center"]1W6[/TD]
[TD="align: center"]25/07/2012[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]100[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]
[TD="align: center"]9G5[/TD]
[TD="align: center"]07/06/2025[/TD]
[TD="align: center"]2500[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]110[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]
[TD="align: center"]6T4[/TD]
[TD="align: center"]20/09/2026[/TD]
[TD="align: center"]2700[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]120[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]
[TD="align: center"]9G5[/TD]
[TD="align: center"]03/01/2028[/TD]
[TD="align: center"]500[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]
[TD="align: center"]7S4[/TD]
[TD="align: center"]04/04/2014[/TD]
[TD="align: center"]3100[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]40[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

then this code should do it:

Code:

Option Explicit: Option Base 1


Sub GroupRecords()
Dim comb, vis As Range, j%, i%, lr%, r%, g%, b%, dup As Boolean, k%
' the combinations
comb = Array(1, 2, 3, 1, 2, 4, 1, 2, 5, 1, 3, 4, 1, 3, 5, _
1, 4, 5, 2, 3, 4, 2, 3, 5, 2, 4, 5, 3, 4, 5)
lr = Range("a" & Rows.Count).End(xlUp).Row
Range("a1:e" & lr).Font.Color = RGB(0, 0, 0)
For k = 2 To lr         ' the records
    On Error Resume Next
    ActiveSheet.ShowAllData
    On Error GoTo 0
    dup = False
    For j = 1 To 10     ' the combinations
        Cells(1, 8).Value = Cells(1, comb(3 * j - 2)).Value
        Cells(1, 9).Value = Cells(1, comb(3 * j - 1)).Value
        Cells(1, 10).Value = Cells(1, comb(3 * j)).Value
        Cells(2, 8).Value = Cells(k, comb(3 * j - 2)).Value
        Cells(2, 9).Value = Cells(k, comb(3 * j - 1)).Value
        Cells(2, 10).Value = Cells(k, comb(3 * j)).Value
        Range("A1:E" & lr).AdvancedFilter Action:=xlFilterInPlace, _
        CriteriaRange:=Range("H1:J2"), Unique:=False
        Set vis = Range("a1:e" & lr).SpecialCells(xlCellTypeVisible)
        If vis.Areas.Count > 2 Then dup = True
        If vis.Areas.Count = 2 Then
            If vis.Areas(1).Rows.Count > 1 Or vis.Areas(2).Rows.Count > 1 Then dup = True
        End If
        If dup Then             ' a group was detected
            r = Int(250 * Rnd)  ' random colors
            g = Int(250 * Rnd)
            b = Int(250 * Rnd)
            For i = 1 To vis.Areas.Count
                If vis.Areas(i).Cells(1, 1).Font.Color = RGB(0, 0, 0) Then _
                vis.Areas(i).Font.Color = RGB(r, g, b)
            Next
        End If
        If dup Then Exit For
    Next
Next
Range("a1:e1").Font.Color = RGB(0, 0, 0)
On Error Resume Next: ActiveSheet.ShowAllData
End Sub

VBA Geek · Mar 3, 2014

I believe a simple conditional format should do the trick for you

Peter_SSs · Mar 3, 2014

@Abhii
If Worf's code does not do what you want, you may need to provide us with a small set of sample data and the expected results with explanation in relation to that sample.

@Worf
I must admit I'm not quite sure what the OP expects or what the data is like but I've changed your sample data little and run your code over it, see below.
I've manually highlighted the two yellow rows and they certainly have at least 3 columns in common (A, B, E).
Similarly the two greeny rows have at least 3 columns in common (A, B, D).
However, rows 6 and 10 also have 3 columns in common (A, C, D) but have been coloured differently by your code.

What the OP would expect if that data is possible I don't know as ..
row 10 is a 'duplicate' of row 3,
row 6 is a 'duplicate' of row 10,
row 13 is a 'duplicate' of row 6,
but
row 13 is not a 'duplicate' of row 3

Excel Workbook

A

B

C

D

E

1

Invoice Number

Date

Amount

Shipment Number

Quantity

2

1W6

25/07/12

500

1

10

3

6T4

7/11/13

700

23

120

4

9G5

20/02/15

2500

21

30

5

9T6

4/04/14

1100

27

40

6

6T4

17/09/17

1300

17

50

7

8H7

31/12/18

1500

13

70

8

8H7

14/04/20

1700

13

70

9

9G5

4/04/14

1900

27

40

10

6T4

7/11/13

1300

17

120

11

1W6

25/07/12

500

19

100

12

9G5

7/06/25

2500

21

110

13

6T4

17/09/17

2700

17

22

14

9G5

3/01/28

500

1

10

15

7S4

4/04/14

3100

27

40

Match at least 3

@VBA Geek
Would you care to share what Conditional Formatting you have in mind?

VBA Geek · Mar 3, 2014

Hello Peter,

from what I understand, his wish is to highlight the duplicate rows, rows are duplicate if 3 or more fields match. so i would use

=ISNUMBER(MATCH(1,IF(((($A2=$A$2:$A$15)+($B2=$B$2:$B$15)+($C2=$C$2:$C$15)+($D2=$D$2:$D$15)+($E2=$E$2:$E$15))>=3)*((ROW($A$2:$A$15)-ROW($A$1))<>ROWS(A$2:A2)),1),0))

if instead he wants to have for each duplicate a diffent colour then of course vba is the way to go

Peter_SSs · Mar 3, 2014

VBA Geek said:
from what I understand, his wish is to highlight the duplicate rows, rows are duplicate if 3 or more fields match. so i would use

=ISNUMBER(MATCH(1,IF(((($A2=$A$2:$A$15)+($B2=$B$2:$B$15)+($C2=$C$2:$C$15)+($D2=$D$2:$D$15)+($E2=$E$2:$E$15))>=3)*((ROW($A$2:$A$15)-ROW($A$1))<>ROWS(A$2:A2)),1),0))

if instead he wants to have for each duplicate a diffent colour then of course vba is the way to go

I'd have to say I wouldn't call that a "simple conditional format"

But thanks for sharing - now we can see if it suits the OP's requirements.

Worf · Mar 3, 2014

Hi Peter

What I understood from the OP’s description was:

- If any three fields on a record match another record, they belong to the same family
- Each family will have a different color, and it will apply to the whole record
- The database is to be flagged in place
- If a record can belong to more than one family, I simply choose one, because a record cannot have two colors at the same time
- So per the above, the following pairings would be acceptable, (6,13) or (3,10) or (6,10).How would you treat this situation?

- I guess we will have to wait and see what Abhii has to say…

Peter_SSs · Mar 4, 2014

Worf said:
How would you treat this situation?

- I guess we will have to wait and see what Abhii has to say…

I don't have an answer to the first point here, so I'm agreeing with the second at this stage.

Macro to search duplicates by matching any 3 of the 5 fields in a record.

Abhijeet_Chib

New Member

Excel Facts

Worf

Well-known Member

VBA Geek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

VBA Geek

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

Worf

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Macro to search duplicates by matching any 3 of the 5 fields in a record.

New Member

Excel Facts

Well-known Member

MrExcel MVP

MrExcel MVP, Moderator

MrExcel MVP

MrExcel MVP, Moderator

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock