PDA

View Full Version : Find duplicate values accross columns per row



KongUK
10-24-2017, 08:18 AM
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
10-24-2017, 09:03 AM
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
10-24-2017, 09:47 AM
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
10-24-2017, 09:49 AM
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
10-24-2017, 11:30 AM
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
10-24-2017, 12:55 PM
for instance ^^ lol
:bow:

KongUK
10-24-2017, 11:49 PM
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
10-25-2017, 04:46 AM
You're welcome. Glad I can offer some help