CODEWITHSUNDEEP
excelduplicates
Ssong
Ssong

Reputation: 452

How to check the duplicate pair in excel?

I tried to find the pairs in multiple columns in excel.

abc def  1 <-duplicate 1
ael fjw  1
dlf qwr  1
cvz god  1 <-duplicate 2
abc def -1 <-duplicate 1
slf erw -1
def abc -1 <-duplicate 1
god cvz -1 <-dupllicate 2
cnv odf -1

After that, I should eliminate the pairs that have the value -1.
I tried excel duplicate values pairs in multiple column post, but it showed an unexpected result.

If it is hard to run in Excel, it is okay to suggest the code in python or R. In particular, I checked the post Removing duplicate interaction pairs in python sets which is a similar problem in python.
But this example is corresponding to the numerical value.

Also, if there are any problems with my question, please correct them.

Upvotes: 0

Views: 388

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

Assuming your first row of data is in A1:C1, this formula in D1:

=IF(AND(SUM(COUNTIFS(A$1:A1,INDEX(A1:B1,{1;2}),B$1:B1,INDEX(A1:B1,{2;1})))>1,C1=-1),"Delete","")

and copied down.

If your version of Excel does not use the semicolon as row- or column-separator within array constants then the parts

{1;2}

and

{2;1}

will require amendment.

Upvotes: 1

Related Questions