Find duplicate values accross columns per row

[KongUK]

Hi
I need a quick way with VBA to fill in data for duplicate column, whereby if Part, Type, and Issue are the same then 'yes' in the duplicate column

Row	Part	Type	Issue	Duplicate
1	Part-A	Nut	2	No
2	Part-C	Bolt	1	Yes
3	Part-B	Washer	2	No
4	Part-C	Bolt	1	Yes
5	Part-B	Nut	2	No
6	Part-B	Nut	3	No
7	Part-A	Nut	1	No

As in the table rows 2 and 4 would be Yes
All the data will exist, column length will be dynamic range, duplicate column will begin empty

I don't mind a formula (in duplicate column) or VBA loop

Thank you

[Paul_Hossler]

In the Dup column and fill down

=IF(COUNTIFS($A$2:$A$8,A2,$B$2:$B$8,B2,$C$2:$C$8,C2)>1,"Yes","No")

Capture.JPG

[MINCUS1308]

Here is some generic code if you have to use VBA.
Ill also attach your example Workbook.

Sub Testing()
'SET THE COLUMN NUMBERS FOR EACH COLUMN HEADER
'JUST INCASE YOUR NOT IN COLUMNS A,B,C,D LIKE THE EXAMPLE
     CPart = 1
     CType = 2
     CIssue = 3
     CDuplicate = 4
     
'SOME CRAZY LOOP STUFF
     i = 2
     Do Until Cells(i, CPart).Value = ""
          j = i + 1
          Do Until Cells(j, CPart).Value = ""
               If Cells(i, CPart).Value = Cells(j, CPart).Value Then
                    If Cells(i, CType).Value = Cells(j, CType).Value Then
                         If Cells(i, CIssue).Value = Cells(j, CIssue).Value Then
                              Cells(i, CDuplicate).Value = "Yes"
                              Cells(j, CDuplicate).Value = "Yes"
                         Else
                         'THIS IS GOING TO MISS SOME :) ILL GET THOSE LATER
                              If Cells(i, CDuplicate).Value = "" Then
                                   Cells(i, CDuplicate).Value = "No"
                              End If
                         End If
                    End If
               End If
          j = j + 1
          Loop
     'WE'LL CATCH ALL THE ONES I MISSED EARILER HERE
          If Cells(i, CDuplicate).Value = "" Then Cells(i, CDuplicate).Value = "No"
     i = i + 1
     Loop
End Sub

[MINCUS1308]

There are definitely more elegant methods you could use But that should get it for you. If I misunderstood the question please let us know

[YasserKhalil]

Try this

Sub Test()
    Dim x       As Variant
    Dim i       As Long
    Dim ii      As Long


    With ActiveSheet
        x = .Cells(1).CurrentRegion.Resize(, 4)
        For i = 2 To UBound(x, 1)
            If x(i, 4) <> "Yes" Then
                For ii = i + 1 To UBound(x, 1)
                    If x(ii, 1) & x(ii, 2) & x(ii, 3) = x(i, 1) & x(i, 2) & x(i, 3) Then
                        x(ii, 4) = "Yes": x(i, 4) = "Yes"
                    End If
                Next ii
            End If
        Next i
        .Cells(1).CurrentRegion.Resize(, 4) = x
    End With
End Sub

[MINCUS1308]

for instance ^^ lol

[KongUK]

OK thanks

I tried YasserKhalil Solution and I am using data from columns 3,4,5, and result to go in column 8, so adjusted thus

With ActiveSheet
        x = .Cells(3).CurrentRegion.Resize(, 8)
        For i = 2 To UBound(x, 1)
            If x(i, 8) <> "Yes" Then
                For ii = i + 1 To UBound(x, 1)
                    If x(ii, 3) & x(ii, 4) & x(ii, 5) = x(i, 3) & x(i, 4) & x(i, 5) Then
                        x(ii, 8) = "Yes": x(i, 8) = "Yes"
                    End If
                Next ii
            End If
        Next i
        .Cells(3).CurrentRegion.Resize(, 8) = x
    End With

Seems to be working ok, just wanted to make sure I had the range covered correctly?

Thank you :=)

[YasserKhalil]

You're welcome. Glad I can offer some help