0

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.

Ssong
  • 184
  • 1
  • 10
  • =A1=B1 then dragged down will do the test giving true or false. Then =if(a1=b1,1,-1) will give the result. Then I would just sort on the result and delete. – Solar Mike Jan 06 '22 at 07:20
  • @Solar Mike Thank you for your comment. I tried your code, but I'm still ambiguous. I want to check if there are any same pairs with 'abc-def' in other rows. 1 and -1 is specific value, not true/positive representor. But your suggestion looks like checking if A1=B1 or not. (i.e. whether abc=def or not) – Ssong Jan 06 '22 at 07:32
  • =A1=B1 means check to evaluate if the contents of cell A1 is equal to the contents of cell B1, whether the contents are text or numbers. – Solar Mike Jan 06 '22 at 07:34

1 Answers1

1

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.

Jos Woolley
  • 8,564
  • 2
  • 4
  • 9