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]

Sub t()
Dim lr As Long, i As Long, fn As Range
    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
                    If fn.Offset(, -1).Value = .Cells(i, 2).Value Then
                        .Rows(i).Delete
                    End If
                End If
        Next
    End With
End Sub

 

[ChrisJP]

JLGWhiz,

This seems to work for a portion of my data, but it seems to fail at a certain point. On my data below, it fails to recognize G006134/G004402 as a duplicate. Any ideas why this might happen? I appreciate the help!

<colgroup><col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> <col style="mso-width-source:userset;mso-width-alt:2121;width:44pt" width="58"> </colgroup><tbody>
[TD="class: xl69, width: 58"]C1
[/TD]
[TD="class: xl75, width: 58"]C2
[/TD]

[TD="class: xl70"]B34558
[/TD]
[TD="class: xl76"]16612[/TD]

[TD="class: xl71"]B41627[/TD]
[TD="class: xl77"]68418
[/TD]

[TD="class: xl71"]G000659[/TD]
[TD="class: xl77"]86130[/TD]

[TD="class: xl71"]G001594[/TD]
[TD="class: xl77"]77539
[/TD]

[TD="class: xl71"]G002224[/TD]
[TD="class: xl77"]96232
[/TD]

[TD="class: xl72"]G002544[/TD]
[TD="class: xl78"]G004514[/TD]

[TD="class: xl73"]G004514[/TD]
[TD="class: xl79"]G002544[/TD]

[TD="class: xl72"]G002771[/TD]
[TD="class: xl78"]G002772[/TD]

[TD="class: xl74"]G002771[/TD]
[TD="class: xl80"]L49435[/TD]

[TD="class: xl73"]G002772[/TD]
[TD="class: xl79"]G002771[/TD]

[TD="class: xl74"]G002772[/TD]
[TD="class: xl80"]L49435[/TD]

[TD="class: xl72"]G002814[/TD]
[TD="class: xl78"]G004402[/TD]

[TD="class: xl72"]G002814[/TD]
[TD="class: xl78"]G006134
[/TD]

[TD="class: xl72"]G002814[/TD]
[TD="class: xl78"]L51316[/TD]

[TD="class: xl72"] G004402
[/TD]
[TD="class: xl78"] G006134 [/TD]

[TD="class: xl73"]G004402[/TD]
[TD="class: xl79"]G002814[/TD]

[TD="class: xl72"]G004402[/TD]
[TD="class: xl78"]L51316
[/TD]

[TD="class: xl72"]G004402[/TD]
[TD="class: xl78"]G006031[/TD]

[TD="class: xl73"]G006031[/TD]
[TD="class: xl79"]G004402
[/TD]

[TD="class: xl72"]G006031[/TD]
[TD="class: xl78"]G006134[/TD]

[TD="class: xl73"] G006134
[/TD]
[TD="class: xl79"] G004402 [/TD]

[TD="class: xl73"]G006134[/TD]
[TD="class: xl79"]G002814[/TD]

[TD="class: xl72"]G006134[/TD]
[TD="class: xl78"]L51316[/TD]

[TD="class: xl73"]G006134[/TD]
[TD="class: xl79"]G006031[/TD]

[TD="class: xl73"]L51316[/TD]
[TD="class: xl79"]G002814[/TD]

[TD="class: xl73"]L51316[/TD]
[TD="class: xl79"]G004402[/TD]

[TD="class: xl73"]L51316[/TD]
[TD="class: xl79"]G006134[/TD]

[TD="class: xl72"]G005639[/TD]
[TD="class: xl78"]L55069[/TD]

[TD="class: xl72"]G005639[/TD]
[TD="class: xl78"]L58082[/TD]

[TD="class: xl72"]G006683[/TD]
[TD="class: xl78"]L58082[/TD]

[TD="class: xl73"]L55069[/TD]
[TD="class: xl79"]G005639[/TD]

[TD="class: xl72"]L55069
[/TD]
[TD="class: xl78"]L58082
[/TD]

[TD="class: xl73"]L58082[/TD]
[TD="class: xl79"]G005639[/TD]

[TD="class: xl73"]L58082[/TD]
[TD="class: xl79"]G006683[/TD]

[TD="class: xl73"]L58082[/TD]
[TD="class: xl79"]L55069
[/TD]

</tbody>

 

[JLGWhiz]

Check for Ohs entered for Zeros. O v. 0

 

[ChrisJP]

Did a search for O vs Zero, no letter O.

 

[JLGWhiz]

Did a search for O vs Zero, no letter O.

I would think it has to be a binary difference of some type, if all the others worked but that one set of characters. Try retyping them and see if they still won't match. I am assuming that you added the bold font and underline for illustration purposes. If not, that is your problem. The underline will not match, but the bold font should.

 

[JLGWhiz]

I was wrong, it should recognize the match, even with the underline.

Try this mod

Sub t()
Dim lr As Long, i As Long, fn As Range
    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
                    If [COLOR=#FF0000]Trim[/COLOR](fn.Offset(, -1).Value) = [COLOR=#FF0000]Trim[/COLOR](.Cells(i, 2).Value) Then
                        .Rows(i).Delete
                    End If
                End If
        Next
    End With
End Sub

 

[ChrisJP]

The mod ended with the same result, still missed some.

The underline was just to highlight one of the examples. There were more missed, the just the one.

Also missed:
L55069/L58082 and L58082/L55069
L51316/G004402 and G004402/L51316

 

[ChrisJP]

I retyped my sample data to see if it was formatting and the problem still occurred.

 

[JLGWhiz]

Well, it has me stymied. I thought the Trim function would fix it if it was not a binary problem. If I think of anything else I will post back.

One other thing. Do you have duplicate entries in column A? Never mind, I see that you do. I think I can fix the problem.