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")


20735

[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
:bow:

[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