Author: Oscar Cronquist Article last updated on February 17, 2023

The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed in column C.

1. Extract duplicates

Update 2017-08-19! New regular formula in cell D3:

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

This video explains how to use the formula and how it works

https://www.youtube.com/watch?v=GE8Z_oSDuoc

The following formula is an outdated formula, the above formula is smaller and better.

Array formula in D3:

=INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0))

1.1 How to enter an array formula

  1. Copy (Ctrl + c) above formula
  2. Double press with left mouse button on cell C2
  3. Paste (Ctrl + v) to cell C2
  4. Press and hold CTRL + SHIFT simultaneously
  5. Press Enter once
  6. Release all keys

Your formula now looks like this: {=array_formula}
Don't enter the curly brackets, they appear automatically.

Recommended articles

test
A beginners guide to Excel array formulas
Array formulas allows you to do advanced calculations not possible with regular formulas.

1.2 How to copy the formula

Copy cell C2 and paste it to cells below as far as needed.

1.3 Remove #num errors:

=IFERROR(INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0)), "")

Copy cell C2 and paste it down to D20.

Learn more:

Recommended articles

test
How to use the IFERROR function
The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]

1.4 Earlier Excel versions, array formula in C2:

=IF(ISERROR(INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))), "", INDEX($A$2:$A$20, MATCH(0, COUNTIF(C1:$C$1, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0)))

The IFERROR function was introduced in Excel 2007, if you have an earlier version then use the formula above.

How (the old) array formula works

Step 1 - Show a duplicate value only once

COUNTIF(D2:$D$2, $B$3:$B$21) contains both a relative and absolute reference (D2:$D$2) to a range.

When you copy a cell reference like this the cell reference expands.

COUNTIF(D2:$D$2, $B$3:$B$21)

returns the array

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

Recommended articles

test
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.

Step 2 - Filter values in $A$2:$A$20 having duplicates

The COUNTIF function counts the number of cells within a range that meet the given condition.

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

returns

{2; 1; 1; 2; 1; 1; 2; 2; 1; 2; 2; 1; 2; 1; 1; 2; 1; 1; 1; 1}.

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

returns

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

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

returns

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

Recommended articles

test
How to use the IF function
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Step 3 - Calculate arrays combined

COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1)

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

equals

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

Step 4 - Identify duplicates

MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0)

becomes

MATCH(0, (1, 0, 0, 1, 0, 0, 1, 1, 0, 1, 1, 0, 1, 0, 0, 1, 0, 0, 0, 0), 0)

returns 2.

Match returns the relative position of an item in an array that matches a specified value.

Recommended articles

test
How to use the MATCH function
Identify the position of a value in an array.

Step 5 - Return duplicates

INDEX function returns a value or reference of the cell at the intersection of a particular row and column, in a given range

INDEX($B$3:$B$21, MATCH(0, COUNTIF(D2:$D$2, $B$3:$B$21)+IF(COUNTIF($B$3:$B$21, $B$3:$B$21)>1, 0, 1), 0))

becomes

INDEX($B$3:$B$21, 2)

and returns "Federer, Roger".

Recommended articles

test
How to use the INDEX function
Gets a value in a specific cell range based on a row and column number.

Final thoughts

When the formula in c2 is copied to c3 the reference changes.

Example

The formula in c2: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D2:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Then copy the formula to C3.

The formula references changes: =INDEX($A$2:$A$20, MATCH(0, COUNTIF(D3:$D$2, $A$2:$A$20)+IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, 0, 1), 0))

Read more about absolute and relative cell references.

This makes it possible to avoid previous cell values (C2) and only calculate the remaining values.

Get the Excel file


how-to-extract-a-list-of-duplicates-from-a-columns-in-excelv2.xlsx

2. Extract duplicates - Excel 365

Extract duplicates Excel 365

Formula in cell D3:

=UNIQUE(FILTER(B3:B21, COUNTIF(B3:B21, B3:B21)>1))

Explaining formula

Step 1 - Count values

The COUNTIF function counts cells based on a condition or criteria, this allows us to identify duplicate values.

COUNTIF(range, criteria)

COUNTIF(B3:B21, B3:B21)

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

and returns

{2; 1; 1; 2; 1; 1; 2; 2; 1; 2; 2; 2; 1; 1; 1; 2; 1; 1; 1}.

Step 2 - Identify duplicates

The larger than sign lets you check if a number is larger than 1, in other words, a duplicate.

COUNTIF(B3:B21, B3:B21)>1

becomes

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

and returns

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

Step 3 - Filter values if count is larger than 1

The FILTER function extracts values/rows based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER(B3:B21, COUNTIF(B3:B21, B3:B21)>1)

becomes

FILTER({"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 "}, {TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "}.

Step 4 - List one instance of each value

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(B3:B21, COUNTIF(B3:B21, B3:B21)>1))

becomes

UNIQUE({"Federer, Roger "; "Davydenko, Nikolay "; "Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "}.