Search code examples
excelmatrixexcel-formuladuplicatesextract

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))

    enter image description here

    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))))))))