## Extract a list of duplicates from two columns combined

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in column F.

Formula in cell F3:

### Explaining formula in cell F3

This formula consists of two similar parts, one returns values from List1 and the other returns values from List2.

IFERROR(*formula1, formula2*)

#### Step 1 - Prevent duplicate values

The COUNTIF function counts values based on a condition, in this case, I am counting values in cells above. This makes sure that duplicates are ignored.

COUNTIF($F$2:F2,$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",$B$3:$B$21)=0

becomes

COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})=0

{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}=0

and returns

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE}

#### Step 2 - Count values in List1

We want to know where the duplicates are if there are any.

COUNTIF($B$3:$B$21,$B$3:$B$21)>1

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy ";"Davydenko, Nikolay ";"Roddick, Andy ";"Del Potro, Juan Martin ";"Federer, Roger ";"Davydenko, Nikolay ";"Verdasco, Fernando ";"Gonzalez, Fernando ";"Wawrinka, Stanislas ";"Gonzalez, Fernando ";"Blake, James ";"Nalbandian, David ";"Robredo, Tommy ";"Wawrinka, Stanislas ";"Cilic, Marin ";"Stepanek, Radek ";"Almagro, Nicolas "})>1

becomes

{2;1;1;2;1;1;2;2;1;2;2;2;1;1;1;2;1;1;1}>1

{TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE}

#### Step 3 - Multiply arrays

(COUNTIF($F$2:F2,$B$3:$B$21)=0)*(COUNTIF($B$3:$B$21,$B$3:$B$21)>1)

becomes

{TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE; TRUE;TRUE;TRUE;TRUE} * {TRUE;FALSE; FALSE;TRUE; FALSE;FALSE; TRUE;TRUE; FALSE;TRUE; TRUE;TRUE; FALSE;FALSE; FALSE;TRUE; FALSE;FALSE; FALSE}

and returns

{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}