Search code examples
excelexcel-2007excel-2010

Excel find duplicate cells in multiple columns

I have an Excel table with 4 columns and need to find the values that are the same in ALL 4 of the columns and put them aside. Trying to figure out how to accomplish this in Excel.

Example:

Column 1    Column 2    Column 3    Column 4
A1          B1          B1          A1
B1          C2          C1          B1
C2          D3          C2          C2

In this example, both B1 and C2 values comply and must be set aside.

Solution

  • Put this formula in cell D1 and drag and drop it till the end of your values:

    =IF(AND(COUNTIF($B$1:$B$3,A1)>0,COUNTIF($C$1:$C$3,A1)>0,COUNTIF($D$1:$D$3,A1)>0),A1,"not found")

    For the record, the french version:

    =SI(ET(NB.SI($B$1:$B$3;A1)>0;NB.SI($C$1:$C$3;A1)>0;NB.SI($D$1:$D$3;A1)>0);A1;"not found")