How to find a reverse pair in excel?-CodePudding

I want to look into two columns and see whether pairs in my excel sheet have reverse pair combination or not. For example if I have 1255, 6584 then is there 6584, 1255 too? How to investigate this?

My file is very long, but suppose we have the following:

CodePudding user response：

Try using =IF(B1<C1,B1&"/"&C1, C1&"/"&B1) in the fourth column. This way you can create a unique identifier for each pair.

If you want you could add =COUNTIF(D:D,D1) as a fifth column to see how many of each unique pair exist.

CodePudding user response：

with Office 365 you could use =TEXTJOIN(", ",1,XLOOKUP(A1:A11&B1:B11,B1:B11&A1:A11,A1:A11&" "&B1:B11,"",0))

This returns all reverse matches from column A/B to column B/A.

The following would return the matches in column A from any row and the row below in column A which matches to any row in column B with ithe row above: =TEXTJOIN(", ",1,XLOOKUP(A1:A10&A2:A11,B2:B11&B1:B10,B2:B11&" "&B1:B10,"",0,-1))