# Excel, extract duplicates from multiple columns

Is there a way to extract the duplicates from 3 different columns? Let's say I have

``````Column A  Column B  Column C
ID1       ID4       ID7
ID2       ID5       ID8
ID3       ID6       ID9
ID98      ID98      ID98
ID99      ID99      ID99
``````

I would like to create a fourth column where I only see the value that are duplicated in each of the 3 columns, to obtain

``````Column D
ID98
ID99
``````

Is there an easy way to do it in Excel/VBA?

I tried to copy multiple formulas but was not able to find a solution

Solution

• ## Unique in 3 Columns

This will return all values that occur at least once in each column.

``````=UNIQUE(TOCOL(XLOOKUP(XLOOKUP(A2:A6,B2:B6,B2:B6),C2:C6,C2:C6),3))
``````

To make it flexible, to work for any (reasonable) number of columns, you could use...

``````=LET(data,A2:C6,
f,TAKE(data,,1),
o,DROP(data,,1),
rs,SEQUENCE(ROWS(f),,1,0),
cs,SEQUENCE(COLUMNS(o)),
c,REDUCE(rs,cs,LAMBDA(rr,c,
rr*ISNUMBER(XMATCH(f,CHOOSECOLS(o,c))))),
dr,UNIQUE(FILTER(f,c)),
dr)
``````

... which would, after removing the 'redundant' variables, result in the following:

``````=LET(data,A2:C6,
f,TAKE(data,,1),
o,DROP(data,,1),
UNIQUE(FILTER(f,REDUCE(
SEQUENCE(ROWS(f),,1,0),
SEQUENCE(COLUMNS(o)),LAMBDA(rr,c,
rr*ISNUMBER(XMATCH(f,CHOOSECOLS(o,c))))))))
``````