Excel – Remove duplicates from 2 columns (also if values are permuted)

Dear readers, recently, on a forum, a person asked how to remove duplicates from 2 columns also if the values are permuted ! For example, (a, b) and (b, a) are a duplicate values of (a, b).

So, this is a quick trick on how to achieve that.

First, we combine the values into a new column :

Add “Combin” column

Next, we add a column in which we count the number of times a value and its inverse appear in the previous rows with the bellow formula, then fill down into the column
= COUNTIF ($D$2:D2, [@[Col 1]]&[@[Col 2]])
+ COUNTIF ($D$2:D2, [@[Col 2]]&[@[Col 1]])

Add “Nb value and inverse Previous rows” column

If we have the same value in “Col1” and “Col2”, the “Combin” value will be counted 2 times, so we divide according to the following condition IF([@[Col 1]]=[@[Col 2]],2,1). The new formula will looks like :

Add the division

And finally, to get the distinct list of values, we just need to keep the results that equals to 1.

Filter the results

If you have a question let me know it.

Keep learning and see you next time 🙂