excel duplicate values pairs in multiple column

Question

Is there a way to find duplicate pairs in multiple columns in Excel?

For instance,

column1       column2  

Smith         Jones       <-- duplicate pair  
Smith         Johnson  
Jones         Smith       <-- duplicate pair  
Jones         Walter  

Answer 1

The way I would do it would be as follows: (Assuming your data begins in cell A1 & B1)

• In C1, put in the formula: =A1&"|"&B1
• In D1, put in the formula: =B1&"|"&A1
• In E1, put in the formula: =MATCH(C1,$D$1:$D$500,0)
• Drag all these down for your dataset (and change the $500 in your final formula if necessary)
• Any place where column E has a number, it means the data is duplicated - You could even filter for anything that isn't #N/A and it tells you where in the list the duplicate match is.

Hope this helps!!