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

[ChrisJP]

Example:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[/TR]
[TR]
[TD]Row 1[/TD]
[TD]ABC123[/TD]
[TD]MNO456[/TD]
[/TR]
[TR]
[TD]Row 2[/TD]
[TD]ZZZ987[/TD]
[TD]YYY789[/TD]
[/TR]
[TR]
[TD]Row 3[/TD]
[TD]MNO456[/TD]
[TD]ABC123[/TD]
[/TR]
</tbody>[/TABLE]

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