# Extract a list of duplicates from a column

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

The following link takes you to an article that shows formulas that extract duplicates from a source range spread out to multiple columns: Extract duplicates from a multi-column cell range

### What's on this page

- Extract duplicates
- Extract duplicates - Excel 365
- Filter duplicates in a large dataset - UDF
- List duplicates based on condition
- Filter duplicate values and sort by corresponding date
- Extract a list of duplicates from two columns combined
- Filter duplicate words from a cell range - UDF
- List duplicate rows / records

## 1. Extract duplicates

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

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

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

Array formula in D3:

### 1.1 How to enter an array formula

- Copy (Ctrl + c) above formula
- Double press with left mouse button on cell C2
- Paste (Ctrl + v) to cell C2
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

Recommended articles

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:

Copy cell C2 and paste it down to D20.

Learn more:

Recommended articles

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:

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

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

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

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

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.

## 2. Extract duplicates - Excel 365

The image above shows a list of values in column B, some of them are duplicates. The formula in cell D3 extracts duplicate values in cell D3 and cells below.

Excel 365 dynamic array formula in cell D3:

The formula extracts only one duplicate per value, remove the UNIQUE function from the formula above to list all instances of each duplicate. The formula spills values to cell D3 and cells below automatically, a #SPILL error indicates that one or more cells below cell C3 are non-empty. Remove the values so the formula can display all duplicates.

### 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 "}.

## 3. Filter duplicates in a large dataset - UDF

This article demonstrates a user defined function that extractsÂ duplicate valuesÂ and alsoÂ count duplicates.

Example, the image below shows a list containing duplicateÂ values.

### User defined function

Function DuplicateValues(rng As Variant, Optional CountDuplicates As Variant) As Variant Dim Test As New Collection Dim Dupes As New Collection Dim Value As Variant Dim Item As Variant Dim temp() As Variant ReDim temp(0) rng = rng.Value If IsMissing(CountDuplicates) Then CountDuplicates = False On Error Resume Next For Each Value In rng If Len(Value) > 0 Then Test.Add Value, CStr(Value) If Err Then If Len(Value) > 0 Then Dupes.Add Value, CStr(Value) Err = False End If Next Value On Error GoTo 0 If CountDuplicates = False Then For Each Item In Dupes temp(UBound(temp)) = Item ReDim Preserve temp(UBound(temp) + 1) Next Item DuplicateValues = Application.Transpose(temp) Else DuplicateValues = Dupes.Count End If End Function

### How to add the user defined function to your workbook

- Press Alt-F11 to open the Visual Basic editor
- Press with left mouse button on Module on the Insert menu
- Copy and paste the above user defined function
- Exit visual basic editor

### How to extract duplicate values

- Select a cell range
- TypeÂ =DuplicateValues($A$1:$F$3000, FALSE)
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys

### How to count duplicate values

- Select a cell
- Type =DuplicateValues($A$1:$F$3000, TRUE) into formula bar and press
*ENTER.*

## 4. List duplicates based on condition

**Question:** How do I filter duplicates based on a condition?

**Answer: **Column B contains category and column C contains Items. Only duplicate Items with adjacent Category number 2 is listed in column E.

AA is in category 2 (row 3) and 1 but exists only once in category 2. It is not a duplicate.

BB is in category 2 and exists twice (row 4 and 9). BB has a duplicate.

CC is in category 2 and has a duplicate (row 6 and 8). CC is a duplicate.

Excel 365 dynamic array formula in cell E4:

Formula for earlier Excel versions in cell E4:

### Explaining formula in cell E4

**Step 1 - Identify values that has not been displayed before**

COUNTIF($E$3:E3, $C$3:$C$9)=0

becomes

COUNTIF("Duplicate items",{"AA";"BB";"AA";"CC";"AA";"CC";"BB"})=0

and returns

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

In cell E4 no values has been shown before so the array returns TRUE for all values.

Recommended articles

Counts the number of cells that meet a specific condition.

**Step 2 - Find duplicates in category 2**

COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1

becomes

COUNTIFS({2;2; 1;2;1; 2;2}, 2, {"AA";"BB";"AA";"CC";"AA";"CC";"BB"}, {"AA";"BB";"AA";"CC";"AA";"CC";"BB"})>1

becomes

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

and returnsÂ {FALSE;TRUE; FALSE;TRUE;FALSE; TRUE;TRUE}

Recommended articles

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

**Step 3 - Multiply arrays**

(COUNTIF($E$3:E3, $C$3:$C$9)=0)*(COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)

becomes

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

and returns {0;1; 0;1;0; 1;1}

**Step 4 - Divide 1 with array**

1/(((COUNTIF($E$3:E3, $C$3:$C$9)=0)*(COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)))

becomes

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

and returnsÂ {#DIV/0!; 1;#DIV/0!; 1;#DIV/0!; 1;1}

**Step 5 - Find last matching value in array**

The LOOKUP function ignores errors but requires the second argument to be sorted ascending. However our list contains only errors or 1 so the LOOKUP function returns the last matching 1 in the array.

LOOKUP(2, 1/(((COUNTIF($E$3:E3, $C$3:$C$9)=0)*(COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1))), $C$3:$C$9)

becomes

LOOKUP(2, {#DIV/0!; 1;#DIV/0!; 1;#DIV/0!; 1;1}, $C$3:$C$9)

becomes

LOOKUP(2, {#DIV/0!; 1;#DIV/0!; 1;#DIV/0!; 1;**1**}, {"AA";"BB";"AA";"CC";"AA";"CC";**"BB"**})

and returns "BB" in cell E4. BB is the corresponding value to the last 1 in the array, bolded above.

Recommended articles

Finds a value in a sorted cell range and returns a value on the same row.

**Step 6 - Relative cell refences**

The following cell reference is both an absolute and relative cell reference:Â $E$3:E3. When you copy the formula to cells below, the cell ref changes. That way the formula knows which values have been displayed before.

Recommended articles

What is a reference in Excel? Excel has an A1 reference style meaning columns are named letters A to XFD […]

### Get excel example file

Filter duplicates with a condition.xlsx

(Excel Workbook *.xlsx)

## 5. Filter duplicate values and sort by corresponding date

This section demonstrates formulas that extract duplicates from cell range B2:B21 and sort the output by the corresponding dates specified in cell range A2:A21.

Excel 365 dynamic array formula in cell D2:

The Excel 365 formula above spills values to cells below and to the right automatically. A #SPILL error indicates that one or more cells are not empty, make sure the cells are empty so the formula can show all values.

Array formula for earlier Excel versions in D2:

Array formula for earlier Excel versions in E2:

### Explaining formula in cell D2

#### Step 1 - Identify duplicates

The COUNTIF function counts values based on a condition or criteria.

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

becomes

COUNTIF({"BB"; "VV"; "WW"; "BB"; "DD"; "KK"; "YY"; "UU"; "LL"; "WW"; "PP"; "CC"; "VV"; "MM"; "EE"; "WW"; "KK"; "TT"; "II"; "PP"}, {"BB"; "VV"; "WW"; "BB"; "DD"; "KK"; "YY"; "UU"; "LL"; "WW"; "PP"; "CC"; "VV"; "MM"; "EE"; "WW"; "KK"; "TT"; "II"; "PP"})>1

becomes

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

and returns

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

#### Step 2 - Convert boolean values

The IF function returns a value based on a logical expression, TRUE returns argument2 and FALSE returns argument3.

IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), "")

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), "")

The COUNTIF function returns a rank number indicating the position if the list were sorted, "<" is concatenated withÂ A$2:$A$21 to make the COUNTIF function behave in this way.

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), "")

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE}, {7; 11; 8; 19; 16; 17; 15; 10; 9; 0; 4; 18; 14; 2; 1; 13; 6; 2; 12; 4}, "")

and returns

{7;11;8; 19;"";17;"";"";""; 0;4;"";14;""; "";13;6;""; "";4}

#### Step 3 - Extract the k/th smallest number in array

The SMALL function returns the k/th smallest number in array ignoring blanks.

SMALL(IF(COUNTIF($B$2:$B$21,$B$2:$B$21)>1,COUNTIF($A$2:$A$21,"<"&$A$2:$A$21),""),ROWS($A$1:A1))

becomes

SMALL({7;11; 8;19;"";17; "";"";""; 0;4;""; 14;"";"";13; 6;"";"";4},ROWS($A$1:A1))

becomes

SMALL({7;11; 8;19;"";17; "";"";""; 0;4;""; 14;"";"";13; 6;"";"";4},1)

and returns 0 (zero).

#### Step 4 - Identify position in cell range

The MATCH function returns the position of a value in a cell range or an array.

MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)

becomes

MATCH(0, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)

becomes

MATCH(0, {7;11;8; 19;"";17;"";"";""; 0;4;"";14;""; "";13;6;""; "";4}, 0)

and returns 10.

#### Step 5 - Return value from cell range

INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0))

becomes

INDEX($A$2:$A$21, 10)

and returns "1/10/2008" in cell D2.

### Get Excel *.xlsx file

Filter duplicate values and sort by datev2.xlsx

## 6. 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:

Excel 365 dynamic array 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}

#### Step 4 - Divide 1 with array

TheÂ LOOKUP functionÂ ignores error and if we divide 1 with 0 an error occurs. 1/0 = #DIV/0!

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

becomes

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

and returns

{1;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;1;1;#DIV/0!;1;1;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!}

#### Step 5 - Return value based on array

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)

becomes

LOOKUP(2, 1/{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, $B$3:$B$21)

becomes

LOOKUP(2, 11/{1;0;0;1;0;0;1;1;0;1;1;1;0;0;0;1;0;0;0}, {"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 Wawrinka, Stanislas in cell F3.

#### Step 6 - Return values from List2

When values run out from List1 formula1 returns errors, theÂ IFERROR function then moves to formula2.

IFERROR(*formula1, formula2*)

formula2 is just like formula1 except that it returns values from List2 and duplicates found between List1 and List2.

#### Earlier Excel versions:

### Get Excel *.xlsx

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

## 7. Filter duplicate words from a cell range - UDF

**AJ Serrano asks:**

I have a column where each rows contains different values and I wanted to obtain the duplicate values in each rows.

SAMPLE

COLUMN A contains these data:

3M - Asia

3MÂ South America

3M - Africa

3M - US

3M - ASIA

3M - Us

3M - South AMERICA

3M - Europe

3MÂ Australia

3M Australia

3M Europe

3M aSIA

3M US

How do I get duplicate values and put them on the next column say column B? 3M is one of the duplicate values.

**Answer:**

Rick Rothstein (MVP - Excel) helped me out here with a powerful user defined function (udf).

Array formula in cell B2:B9

Select all the cells to be filled, then type the above formulaÂ into the Formula Bar and press CTRL+SHIFT+ENTER

Array formula in cell C2:C9

Select all the cells to be filled, then type the above formulaÂ into the Formula Bar and press CTRL+SHIFT+ENTER

### User defined function

Instructions

- You can select far more cells to load the formulas in than are required by the list. The empty text string will be displayed for cells not having an entry.
- You can specify a larger range than the there are filled in cells as the argument to these macros to allow for future entries in the column.
- You can specify whether the listing is to be case sensitive or not via the optional second argument with the default value being FALSE, meaning duplicated entries with different casing like One, one, ONE, onE, etc.. will all be treated as if they were the same word with the same spelling. If you pass TRUE for that optional second argument, then those words would all be treated as if they were different words.
- For all the "Case Insensitive" listing, the words are listed in Proper Case (first letter upper case, remaining letters lower case). The reason being if you had One, one and ONE then there is not reason to prefer one version over another, so I solved the problem by using Proper Case throughout.

VBA Code:

Function DuplicatedWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant Dim X As Long, WordCount As Long, List As String, Duplicates As Variant, Words() As String List = WorksheetFunction.Trim(Replace(Join(WorksheetFunction.Transpose(Rng)), Chr(160), " ")) Words = Split(List) For X = 0 To UBound(Words) If CaseSensitive Then If UBound(Split(" " & List & " ", " " & Words(X) & " ")) > 1 Then Duplicates = Duplicates & Words(X) & " " List = Replace(List, Words(X), "", 1, -1, vbBinaryCompare) End If Else If UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & " ")) > 1 Then Duplicates = Duplicates & StrConv(Words(X), vbProperCase) & " " List = Replace(List, Words(X), "", 1, -1, vbTextCompare) End If End If Next Duplicates = WorksheetFunction.Trim(Duplicates) Words = Split(Duplicates) If Application.Caller.Count > UBound(Words) Then Duplicates = Duplicates & Space(Application.Caller.Count - UBound(Words)) End If DuplicatedWords = WorksheetFunction.Transpose(Split(Duplicates)) End Function

**How to implement user defined function in excel**

- Press Alt-F11 to open visual basic editor
- Press with left mouse button on Module on the Insert menu
- Type your user defined function
- Exit visual basic editor
- Select a sheet
- Select a cell range
- Type =DuplicatedWords($A$2:$A$18, TRUE) into formula bar and press CTRL+SHIFT+ENTER

**Get Rick Rothstein's Excel example file**

**Many thanks to Rick Rothstein (Mvp - Excel)!!**

## 8. List duplicate rows / records

This article describes how to filter duplicate rows with the use of a formula.Â It is, in fact, an array formula which is demonstrated below. The function that makes this all possible is the COUNTIFS function, introduced in Excel 2007.

The COUNTIFS function evaluates criteria to cells across multiple ranges and counts the number of times all criteria are met.

Array formula in cell A30:

Copy cell A30 and paste it to the right as far as needed. Then copy cells and paste them down as far as needed.

Excel 365 dynamic array formula in cell B14:

### How this formula works in cell A30

*Step 1 - Find duplicates*

=INDEX($A$2:$D$25, SMALL(IF(**COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1**, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1)), COLUMN(A1)

COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1

returns this array:

{False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}

*Step 2 - Use array to extract row numbers*

IF({False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1)

becomes

IF({False, False, True, False, False, False, True, True, True, True, True, True, True, False, True, True, True, True, True, False, True, True, True, True}, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24})

and returns this array:

{False, False, 3, False, False, False, 7, 8, 9, 10, 11, 12, 13, False, 15, 16, 17, 18, 19, False, 21, 22, 23, 24}

*Step 3 - Return the k-th smallest value*

SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1))

becomes

SMALL({False, False, 3, False, False, False, 7, 8, 9, 10, 11, 12, 13, False, 15, 16, 17, 18, 19, False, 21, 22, 23, 24}, ROW(A1))

and returns 3.

*Step 4 - Return a value of the cell at the intersection of a particular row and column*

=INDEX($A$2:$D$25, SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 3, COLUMN(A1))

becomes

=INDEX($A$2:$D$25, 3, 1) and returns "Sample1"

*Final notes*

The formula uses relative and absolute cell references.

In cell B30 the array formula changes cell references to:

=INDEX($A$2:$D$25, SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(A1)), COLUMN(**B1**))

In cell A31 the formula changes cell references to:

=INDEX($A$2:$D$25, SMALL(IF(COUNTIFS($A$2:$A$25, $A$2:$A$25, $B$2:$B$25, $B$2:$B$25, $C$2:$C$25, $C$2:$C$25, $D$2:$D$25, $D$2:$D$25)>1, ROW($A$2:$A$25)-MIN(ROW($A$2:$A$25))+1), ROW(**A2**)), COLUMN(**A2**))

### Get excel sample file for this tutorial.

Filter-duplicate-rows-in-excel-2007v3.xlsx

(Excel 2007 Workbook *.xlsx)

### Duplicate values category

This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]

This article explains how to extract values that exist in three different columns, they must occur in each of the […]

Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]

### Excel categories

### 56 Responses to “Extract a list of duplicates from a column”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Hi,

I want to thank you for the great fuction you created. They are very handy.

One technical question. I have some problem to make array that are include more than 500 cells. Do you know how this can be fixed. My guess will be that this is some memory limitation, but I am speculating. I look in the vbs code and did not see any size limitations there.

Your reply will be highly appreciated.

Krassy

Hello, I have the same issue with limit of just over 500, I am using old version of excel, perhaps this is the case, can you tell me if it was fixed and what fixed it, thank you

@Krassy,

Off the top of my head, I do not see where there should be any limitation as your are reporting. If you would like, you can email me your workbook along with a description of the steps you take that produces the problem (so I can duplicate them here) and any error messages you get (so I know what to look for) and I will see if I can uncover the problem. My email address is rickDOTnewsATverizonDOTnet... just replace the upper case letters with the words they spell out.

No mention of Nadal :D I'm guessing you're a Federer fan Thumbs UP

Hello,

I have duplicate data spread between C3 and W22. I would like to list the distinct values in C3:W22 in another worksheet(2003). could you please help me.

Regards,

Kamal

I tried this formula, it does not work for me - any way to figure out what I am doing wrong??

Sheila McGarrigle,

How to create an array formula

Select cell C2

Copy/paste array formula

Press and hold Ctrl + Shift

Press Enter

how to extract the unique distinct column from one sheet to another sheet in the same work book using formula.

audithya,

Extract a unique distinct list

Change cell references.

i changed the cell reference but the value is showing as " 0".

what i want is

sheet 1 - column A has 123412323422 values (Having blank cells).

i want this unique distinct values to sheet2 column A..

please provide the formula to get the values into sheet 2 from sheet 1.

thanqq

Audithya

added to the above one

i want the result in sheet 2 column A as 1234(without blank cells)

Here is an example workbook:

audithya.xlsx

Oscar,

I used this example workbook in my own application and it works great. I'm wondering if there is a way to filter the results and/or criteria. In the duplicates column I have a wide range of text-filled cells, which duplicate quite frequently. I would like to have this formula return only cells that begin with the letter "L", and also eliminate all duplicates. Is this possible?

Example

Column A, Sheet 1

APPLE

BAKER

LOT1

LOT3

FARM1

FARM1

TABLE

LOT1

LOT4

LOT3

Returning data to Column A, Sheet2

LOT1

LOT3

LOT4

(Not necessarily in a sorted order)

MikeB

Read this:

Filter unique distinct values beginning with a letter

thanqq oscar... :)

Hi Oscar..

I have two Sheets in a workbook. one column of the first sheet contains first sheet contains duplicate data. in the second sheet the column have the data which is extracted the unique data from the first sheet. what i want is the comparing these two columns data if they are equal then the next column result in sheet 1 will be come on the sheet 2

Ex.

Sheet 1:

ColA ColB

a Pass

a Fail

ab Pass

abc Pass

abcd Pass

abcde Fail

abc Fail

Sheet2: wants to be look like

ColA ColB

a Pass

ab pass

abc pass

abcd pass

abcde fail

this colB of sheet 2 extracts the data from colB of sheet 1 if sheet2!ColA=Sheet1!ColA.

Please provide the formula. I am poor in VBA

saiaudithya

Formula in cell B1, sheet 2:

Index(Sheet1!$B$1:$B$10,MATCH(A1,Sheet1!$A$1:$A$10,0))

[...] Re: Duplicates Originally Posted by proficient I want to find duplicates numbers in a range Duplicates Value A B C D 1 1 1 4 2 2 2 6 3 3 4 3 4 4 5 6 6 1 7 2 8 2 9 4 10 65 11 1 12 2 13 2 14 4 15 25 16 1 17 2 Spreadsheet Formulas Cell Formula D1 =COUNTIF($A$1:$A$17,C1) D2 =COUNTIF($A$1:$A$17,C2) D3 =COUNTIF($A$1:$A$17,C3) Excel tables to the web >> Excel Jeanie HTML 4 This seems to be what you are looking for: Extract a list of duplicates from a column using array formula in excel | Get Digital Help - Microso... [...]

It works for me.. Thanks men..

Will it really takes time to if the data is Big?

Tnx,

MIke, CPA

Jarvin Villones,

If you have a large data set it will take time. It all depends on your computer hardware.

Hiya Oscar,

Thank you for your awesome formula, its a bit too advanced for me to be honest but I do grasp the concept of it. I would like to ask you is it possible to encapsulate the formula in an if statement somehow? or as it is in my case "IF from London + IF this month + then PULL unique agent names" I did give it a try but the formula could not work :(

Thanks in advance!

Best Regards,

FB

Neophyte,

Read this:

Extract duplicates using conditions

Huge thank you for your fast response however this is truly outside my league :D (our office excel guru's league too as it seems)... I am trying to pull the "Unique" names rather than duplicates and I don't understand your formula in order to reverse it.

Any help on the subject will be highly appreciated!

THANK YOU!

Neophyte,

Array formula in cell A6:

Get the Excel *.xlsx file

Extract-duplicates-using-conditions_ver2.xlsx

The formulas can be smaller if you have space for a "helper column" in your sheet. The dates make the formulas complicated.

Hi Oscar

Extract a list of duplicates from a column using array formula, does not work for me ? I am using Ms Office 2012

Julio,

did you create an array formula?

Hi, very useful info here.. I can't seem to leave a comment on previous post "How to extract a unique distinct list from a column in excel" so i posted a reply to this thread instead. Sorry..

Anyways, my problem is I wanted to get the unique list only if one condition in one cell is True (Column B). I tried using if() statement but i guess there's something wrong. I know it's very easy for you.. Tnx a lot for your help.

Ex:

A B

1111 True

1232 False

1234 True

Lester,

Read this, I think it is what you are looking for.

Thanks a lot Oscar for your help... Just what i needed... :))

Hi Oscar,

I am using the unique formula you created in response to Neophyte in the comment above. However, I would like to copy the formula across instead of down. How would I modify the formula to accomplish this?

Thank you for the help!

Kyle

Hi Oscar,

Disregard my last question. I figured it out. I just changed the last row formula to column and it worked.

Thanks,

Kyle

How to convert(transpose) sing column to row.

Thanks,

Chethan kumar

How to convert(transpose) single column to row.

Thanks,

Chethan kumar

Reply

Hi,

Just wondering if I would use this formula to return a list so it would show all duplicates as one, and single entries as they are

ie This List: Green, Yellow, Red, Yellow, Blue, Black, Green, Yellow, Oragne, Blue, Green, Pink

To A list like this: Green, Yellow, Red, Blue, Black, Orange, Pink

Thanks

Got it, not to worry.

Hi Oscar,

I am having issues with the listing formula, I have a table as below:

S.no Month Date Session Name Session Duration Trainer

1 Sep 05-Sep-14 The Art of Tactful conversations 0.5 ABC

2 Sep 09-Sep-14 Managing Client Expectations 0.5 DEF

3 Sep 11-Sep-14 Creativity and Lateral Thinking 0.5 SBC

4 Sep 15 Sep14 and 16 Sep Shaping Customer Agenda 2 days ABC

5 Sep 16-Sep-14 The Art of Presentation Skills 0.5 SBC

6 Sep 23-Sep-14 Happiness @ Work 0.5 ABC

7 Sep 25-Sep-14 Emotional Intelligence 1 day DEF

8 Sep 29-Sep-14 Influencing and Negotiation skills 0.5 DEF

9 Sep 29-Sep-14 Planning and Prioritisation 0.5 SBC

10 Sep 30-Sep-14 Strengthening Workplace relations 0.5 ABC

I need to get a list of trainings done if i update a trainers name.

This has to be done in a seperate workbook.

Please help.

Thanks

DS

Iam trying to add this formula for my work but it doesnt work and it comes up with #Name? Please help

thnx

This function seems to ignore any entry in List1 that has only one entry... Is that correct?

Tried this function using a list from a different sheet in by workbook, for some reason it is listing the results in doubles. Any reason for this? Using Excel 2013.

Nate

Hard to say without seeing your workbook, you entered it as an array formula?

Hi Oscar,

Thanks for your formula, I would like to know how can the duplicate be shown in row and expand to the right? Please help!

Jackie,

(Press with left mouse button to see full size image)

The formula is the same:

=INDEX(List1, MATCH(0, COUNTIF(C1:$C$1, List1)+IF(COUNTIF(List1, List1)>1, 0, 1), 0)) + CTRL + SHIFT + ENTER.

Make sure the cell ref (bolded) is pointing to the next cell to the left, however this means you can't enter the formula in the first column.

I was able to adjust this to my project, but man does it take a long time to calculate. I have a spread sheet with about 4000 rows and it can take hours to calculate. Is there a faster way of doing this?

Hi Oscar, stumbled across this formula and it's quality!!! Your formula nearly works perfectly for me but how do I introduce an exception? Your formula finds and retrieves duplicate tennis players names, however if I wanted to exclude the name "Federer, Roger" from my results how would I do this? The list I am checking for duplicates has some legitimate duplicate text which I need to exclude from the returned results, thanks

Ben,

Thank you!

I have added your question to this post.

Read this: https://www.get-digital-help.com/extract-a-list-of-duplicates-from-a-column-using-array-formula-in-excel/#exceptions

Step 1: Add a new column next to your data-field column, called count

Step 2: insert 1 in the first field and drag-to cover the full length of the data-set so that you have count=1 for all rows

Step 3: insert a pivot table with data-field column and count column

Step 4: drag data-field column header to rows

Step 5: drag count column to values and select SUM() function

Now you can see the data-fields listed on the left side with cardinality against each one of them.

No complex formulas are needed to find repeating values.

Satheesh,

No complex formulas are needed to find repeating values.I am trying to provide all possible techniques, some people want a formula.

You don't have to add a new column containing 1 in each cell to identify duplicates:

https://www.get-digital-help.com/2016/07/04/excel-pivot-tables/#count

Thank you for your comment.

[…] Filter duplicate words from a cell range in excel (udf) […]

Hi All,

I'm well aware that this article is almost 10yo but I happen to need this exact VBA code (or ideally one that will highlight the duplicate words from several strings texts across a selected range). HoweverI'm having issue getting this to work for me. I keep getting the "Syntax error message".

Any chance someone can help?

Thank you

Hi Ely

I keep getting the "Syntax error message".The code shown in this article is now working.

Is it possible to list all duplicates using FREQUENCY formula?

The formulas with countif doesn't allow to start from the very first row due to circular reference.

Hi Oscar, I am a fan of your page and your work. I really thank you for helping me a lot. Could you get the duplicate values â€‹â€‹only once and in turn get unique values? How would you do it? Example I have sheet 1:

column A, column B

dario perez

dario perez

dario arrieta

dario arrieta

dario sandoval

The result recorded in columns c and d would be:

column C, column D

dario perez

dario arrieta

dario sandoval

Thanks!

This formula actually doesn't bring back correct results. If an item is a duplicate but wasn't a result in the prior row it is missing it. I replicated tis formula for my file and tested the results., FYI

Hi, I need little help, I have a data of thousand of customer accounts and their account maintaining branch Code. I have been assigned a task to extract branches list and its accounts details according to the given criteria;

I have to extract all those branches and its accounts where a customer have multiple accounts with one CIF# in same branch, for example A customer has CIF # 1234 in 001 branch (its a branch code) 10 accounts are linked with this CIF# in the same branch and there are 05 other accounts which are linked with this CIF but maintained in other different branches; I have to extract the data branches & the accounts where one CIF has multiple accounts in same branch.

here is my table for sample

Branch Code CIF# A/C#

001 123 001001230002151

001 123 002001230002351

001 123 003000123000546

002 123 004000123000445

Hi Dr,

I excited if you could help me work out how to approach the following challenge:

I have a table with repeated IDs in one columns, and dates of when they were admitted in another.

I need to calculate the time spend measure between two dates for the repeated IDs.

Many thanks direction on this one!