How do I identify and delete rows with matching data mixed between multiple columns?

[ChrisJP]

Example:

	Column 1	Column 2
Row 1	ABC123	MNO456
Row 2	ZZZ987	YYY789
Row 3	MNO456	ABC123

<tbody>
</tbody>

How could I identify Row 1 and Row 3 as being matches/duplicates rows, when the columns may have the data swapped?

Ideally I would want the a VB script or way to delete the Row 3 automatically since it is a duplicate.

This may be basic, but I've been searching for a while now and can't quite find the answer. I appreciate any help you can provide.

 

[JLGWhiz]

This should do it

Sub t3()
Dim lr As Long, i As Long, fn As Range, fAdr As String
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        For i = lr To 2 Step -1
            Set fn = .Range("B:B").Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    Do
                        If Trim(fn.Offset(, -1).Value) = Trim(.Cells(i, 2).Value) Then
                            .Rows(i).Delete
                        End If
                        Set fn = .Range("B:B").FindNext(fn)
                    Loop While fn.Address <> fAdr
                End If
        Next
    End With
End Sub

 

[Fazza]

how about a formula, like

<b8,a8&"|"&b8,b8&"|"&a8)[ code]<b2,a2&"|"&b2,b2&"|"&a2)
= IF (A8 is less than B8, A8&"|"&B8, B8&"|"&A8)

to make a code of "earlier alphabetically" "|" "later alphabetically"

then in next column

=COUNTIF($C$1:C2,C2)>1

the records returning TRUE to then be deleted

would that be OK?</b8,a8&"|"&b8,b8&"|"&a8)[>

 

[Fazza]

Well, that formula didn't paste. A problem with posting less than symbols. The formula was
=if( a2 is less than b2, a2 & '|" & b2, b2 & "|" & a2 )

 

[ChrisJP]

This should do it

Sub t3()
Dim lr As Long, i As Long, fn As Range, fAdr As String
    With ActiveSheet
        lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
        For i = lr To 2 Step -1
            Set fn = .Range("B:B").Find(.Cells(i, 1).Value, , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    Do
                        If Trim(fn.Offset(, -1).Value) = Trim(.Cells(i, 2).Value) Then
                            .Rows(i).Delete
                        End If
                        Set fn = .Range("B:B").FindNext(fn)
                    Loop While fn.Address <> fAdr
                End If
        Next
    End With
End Sub

This worked perfectly! I really appreciate the help JLGWhiz. You've saved me days of manually finding duplicates in 2 columns and deleting them with this script. This will allow me to clean up a 100k records.

 

[JLGWhiz]

This worked perfectly! I really appreciate the help JLGWhiz. You've saved me days of manually finding duplicates in 2 columns and deleting them with this script. This will allow me to clean up a 100k records.

Happy to help,
Regards, JLG