Search code examples
excelexcel-2010excel-2007excel-2013excel-2016

How to find duplicated combination were order does not matter in excel

How to find duplicated combination in excel were order does not matter. I have tried using concatenation and sorting them out but its a waste of space, if only there was another way to achieve this without concatenating. Id truly appreciate it.

Example:

enter image description here

Solution

  • For exact 4 columns and up to 1000 rows:

    {=IF(SUM(IF(MMULT({1,1,1,1},TRANSPOSE(COUNTIF($A1:$D1,$A$1:$D$1000)))=4,1))>1,"duplicate","unique")}

    This is an array formula. Input it into E1 without the curly brackets. Then press [Ctrl]+[Shift]+[Enter] to confirm.

    Copy downwards as needed.

    If it not works, please check the language version of your Excel and the locale of your Windows. Maybe the array constant {1,1,1,1} in my formula must be written as {1\1\1\1} or {1.1.1.1} because the comma will be in conflict with the decimal separator or list delimiter.