excelmatrixexcel-formuladuplicatesextract# Excel, extract duplicates from multiple columns

## Unique in 3 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

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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel