How to find duplicate combinations in Excel

Hi,  I have a spreadsheet that contains multiple duplicate combinations of items.  I would like to remove the duplicates but only based on a duplicate combination of the items between 2 separate columns:

example of original:

A             B        
test1      test2
test3      test4
test2      test1

In my case I consider row 1 and row3 to be duplicate because I don't care about the column order in which they appear.  I am more conscerned with eliminating duplicate combinations.  (i.e.-  (test1,test2 is the same as test2,test1)).  Therefore my desired result after removing the duplicate combinations should be the following:

A            B
test1     test2
test3     test4


or


A           B
test3     test4
test2     test1


Is there a formula i can apply to a worksheet that can delete or highlight these duplicate combinations between two separate columns?

thanks
FREDARCEAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ProfessorJimJamMicrosoft Excel ExpertCommented:
first sort your data  and then select both columns then go to DATA tab and then select remove duplicates  then select only column A i mean the one based on which you want to delete duplicate.

for sorting with macro use below code.  if you want formula solution then in another column put the formula =IFERROR(VLOOKUP(B1,A:A,1,0),"") and then copy and paste as value and then go to data and follow as abovementioned.

for sorting use the below macro  .

see attached file.

Sub test()
    Dim a, i As Long, ii As Long, w, x, n As Long
    With Range("a3").CurrentRegion
        a = .Value
        .ClearContents
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(a, 1)
                If a(i, 1) <> "" Then
                    If Not .exists(a(i, 1)) Then
                        ReDim w(1 To UBound(a, 2))
                        w(1) = a(i, 1): .Item(a(i, 1)) = w
                    End If
                End If
            Next
            For i = 1 To UBound(a, 1)
                If a(i, 2) <> "" Then
                    If Not .exists(a(i, 2)) Then
                        ReDim w(1 To UBound(a, 2))
                    Else
                        w = .Item(a(i, 2))
                    End If
                    For ii = 2 To UBound(a, 2)
                        w(ii) = a(i, ii)
                    Next
                    .Item(a(i, 2)) = w
                End If
            Next
            x = Application.Transpose(Application.Transpose(.items))
            n = .Count
        End With
        .Resize(n).Value = x
    End With
End Sub

Open in new window

EE.xlsm
ProfessorJimJamMicrosoft Excel ExpertCommented:
please also see attached version solution with formula.
EE.xlsm
Ejgil HedegaardCommented:
Or use this formula in C2, and copy down.
=IF(COUNTIFS($A$1:A1,A2,$B$1:B1,B2)+COUNTIFS($B$1:B1,A2,$A$1:A1,B2)>0,"Duplicate","")
Use filter for Duplicates in column C and delete.
Finally delete column C.

If many rows, copy the formulas, and paste special as values.
Sort on column C, and delete the duplicates, and then delete column C.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

FREDARCEAuthor Commented:
I tried this but did not get the desired result.  All it did was sort the data within each column and removed duplicates within  each column.
ProfessorJimJamMicrosoft Excel ExpertCommented:
can you upload a sample data? how did it not work?
FREDARCEAuthor Commented:
I am attaching the file.  The columns I want to find and remove duplicates for are in  columns A and L

thanks
matrix.xlsx
Ejgil HedegaardCommented:
File with duplicates in columns A and L removed.
Some of the values have different comments in column Q, and only the first remains.
A couple of examples:
Apply filter to the original data:
Select "Juniper Edge Firewall / 106" and "Juniper Edge Firewall / 206" in both columns.
Or "Juniper Voice Firewall / 106" and "Juniper Voice Firewall / 206" in both columns.
matrix-duplicates-A-and-L-removed.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FREDARCEAuthor Commented:
Thank you Ejgil

Did you have to do anything different? from the steps you proposed previously?
Ejgil HedegaardCommented:
Same principle using column A and L instead of column A and B.

But the header lines should not be removed.
So skip the compare if column Q is Comment added to the formula.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.