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

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

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

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

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 - Display 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.

returns the array

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

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

Recommended articles

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; ... ; 1}.

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

returns

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

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

returns {1; 0; 0;... ; 0}

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} + {1; 0; 0; ... ; 0}

equals {1; 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)

returns 2.

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

Recommended articles

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

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)

returns {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

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

returns {"Federer, Roger "; "Davydenko, Nikolay "; ... ; "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))

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

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

How to extract duplicate values

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

How to count duplicate values

1. Select a cell
2. 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

returns {TRUE;TRUE;TRUE;... ;TRUE}

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

Recommended articles

Step 2 - Find duplicates in category 2

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

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

Recommended articles

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)

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

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)

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

Recommended articles

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

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

returns {TRUE; TRUE; TRUE; ... ; 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), "")

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.

returns {7;11;8; ... ;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))

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)

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

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

returns

{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

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

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

returns {1;#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)

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

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

1. 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.
2. 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.
3. 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.
4. 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

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Type your user defined function
4. Exit visual basic editor
5. Select a sheet
6. Select a cell range
7. 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

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, ... , True}

Step 2 - Use array to extract row numbers

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

returns this array:

{False, False, 3, ... , 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))

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

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)

9. Extract a list of alphabetically sorted duplicates from a column

The following formulas extracts duplicate values sorted from A to Z from cell range B3:B21.

Excel 365 dynamic array formula in cell C2:

The formula above extracts all duplicates and sorts them alphabetically. However, if you want only one instance of each duplicate then use this formula:

The following formula is for older Excel versions, array formula in C2:

How to enter an array formula

Skip these steps if you are an Excel 365 subscriber. Excel 365 lets you enter all kinds of formulas as regular formulas.

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

The formula now looks like this: {=arrayformula}

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

Explaining formula in cell C2

Step 1 - Count values

The COUNTIF fucntion counts values based on a condition or criteria, this way we can identify which values are duplicates and which are not.

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

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

Step 2 - Display first instance of each duplicate

The following COUNTIF function makes sure that the output list only contains unique values. The first argument contains a cell reference that expands as the formula is copied to cells below.

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

returns {TRUE; TRUE; TRUE; ... ; TRUE}.

Step 3 - Multiply arrays

Both values must return TRUE.

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

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

Boolean	Boolean	Multiply
FALSE	FALSE	0
FALSE	TRUE	0
TRUE	TRUE	1

Step 4 - Replace TRUE with a number representing the order if list were sorted

The IF function returns a value based on a logical expression, if TRUE the second argument is returned, if FALSE the third argument.

IF(COUNTIF($B$3:$B$21,$B$3:$B$21)*(COUNTIF(D2:$D$2,$B$3:$B$21)<>1)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")

returns {7;"";"";... ;""}.

Step 5 - Get smallest value in array

The MIN function returns the samllest value in a cell range or array.

MIN(IF(COUNTIF($A$2:$A$20, $A$2:$A$20)*IF(COUNTIF(C1:$C$1, $A$2:$A$20)=1, 0, 1)>1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""))

returns 3.

Step 6 - Replace TRUE in array with a number representing the order if list were sorted

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

returns {7;"";"";... ;""}.

Step 7 - Return relative position

MATCH(MIN(IF(COUNTIF($B$3:$B$21,$B$3:$B$21)*(COUNTIF(D2:$D$2,$B$3:$B$21)<>1)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),"")),IF(COUNTIF($B$3:$B$21,$B$3:$B$21)>1,COUNTIF($B$3:$B$21,"<"&$B$3:$B$21),""),0)

returns 4.

Step 8 - Return value

The INDEX function returns a value from a cell range based on a row and column number. The cell range is a single column so the column number is not necessary.

INDEX($A$2:$A$20, MATCH(MIN(IF(COUNTIF($A$2:$A$20, $A$2:$A$20)*IF(COUNTIF(C1:$C$1, $A$2:$A$20)=1, 0, 1)>1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), "")), IF(COUNTIF($A$2:$A$20, $A$2:$A$20)>1, COUNTIF($A$2:$A$20, "<"&$A$2:$A$20), ""), 0))

returns "Davydenko, Nikolay " in cell D3.

Get Excel *.xlsx file

how-to-extract-a-list-of-duplicates-sorted-a-to-z-from-a-columnv2.xlsx

10. Filter duplicates within same date, week or month

The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date.

Formula in E3:

Copy cell and paste it to cell range E3:F5.

10.1 Explaining formula in cell E3

Step 1 - Keep track of previous values

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions. If a date and corresponding item already has been displayed the function returns 1. The cell references grow when the cell is copied to cells below, the formula keeps track of previously shown values.

COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0

returns {TRUE; TRUE;... ; TRUE}

Step 2 - Find duplicates

COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1

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

and returns {TRUE; TRUE; ... ; TRUE}

Step 3 - Multiply arrays

We use AND logic because both conditions must be met.

(COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1)

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

Step 4 - Divide 1 with array

The LOOKUP function ignores error values and this is what we are going to use. In order to get an error if a value in the array is FALSE or 0 (zero) we divide 1 with 0 and excel returns !DIV/0 error.

1/((COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1))

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

Step 5 - Get value

The LOOKUP function returns a value on the corresponding row if it is not an error value, simplified.

LOOKUP(2, 1/((COUNTIFS($E$2:$E2, $B$3:$B$11, $F$2:$F2, $C$3:$C$11)=0)*(COUNTIFS($B$3:$B$11, $B$3:$B$11, $C$3:$C$11, $C$3:$C$11)>1)), B$3:B$11)

returns 40189 (1/11/2010) in cell E3.

Get Excel *.xlsx file

Filter-duplicates-within-same-date-week-month-year.xlsx

10.2 Filter duplicates within same week

Formula in B16:

Array formula in F16:

Copy cell and paste it down as far as needed.

Array formula in G16:

Copy cell and paste it down as far as needed.

10.3 Filter duplicates within same month

Array formula in F29:

Copy cell and paste it down as far as needed.

Array formula in G29:

Copy cell and paste it down as far as needed.

Get the Excel File here

Filter-duplicates-within-same-date-week-month-year.xls
(Excel 97-2003 Workbook *.xls)

11. Label groups of duplicate records

Answer

The following array formula assigns unique records the label "d0" and duplicate records the same label

Array formula in cell B3:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

How to copy array formula

1. Select cell B3.
2. Copy cell (Ctrl + c).
3. Select cell range B4:B11.
4. Paste to cell range (Ctrl + v).

Explaining formula in cell B3

Step 1 - Identify if record is unique

The COUNTIFS function allows you to count cells based on multiple conditions.

COUNTIFS($D$3:$D$11, D3, $E$3:$E$11, E3, F$3:$F$11, F3, $G$3:$G$11, G3, $H$3:$H$11, H3)=1

becomes

COUNTIFS({"Europe"; "Europe"; "Europe"; "Europe"; "Europe"; "Europe"; "Europe"; "Europe"; "Europe"},"Europe",{"Spain"; "Spain"; "Spain"; "Italy"; "Italy"; "Italy"; "Italy"; "Italy"; "Spain"},"Spain",{"Apples"; "Apples"; "Tomato"; "Tomato"; "Tomato"; "Cucumber"; "Carrot"; "Cucumber"; "Tomato"},"Apples",{"Green"; "Green"; "Red"; "Green"; "Red"; "Green"; "Red"; "Green"; "Red"},"Green",{10; 10; 10; 10; 10; 10; 10; 10; 10},10)=1

becomes

2=1

and returns the boolean value FALSE. It means that the record is not unique, in other words, there is  at least one other duplicate record.

Step 2 - Return "d0" if record is unique

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

The previous step returns TRUE or FALSE an we are going to use that in our IF function.

IF(COUNTIFS($D$3:$D$11, D3, $E$3:$E$11, E3, F$3:$F$11, F3, $G$3:$G$11, G3, $H$3:$H$11, H3)=1, "d0", remaining_formula)

becomes

IF(FALSE, "d0", remaining_formula)

and the calculation continues in the last IF argument.

Step 3 - Is record the first of all duplicate records?

COUNTIFS($D$3:D3, D3, $E$3:E3, E3, $F$3:F3, F3, $G$3:G3, G3)=1

becomes

1=1

and returns TRUE.

Step 4 - Calculate number if record is first record

IF(COUNTIFS($D$3:D3, D3, $E$3:E3, E3, $F$3:F3, F3, $G$3:G3, G3)=1, "d"&(SUM(1/COUNTIFS($D$3:D3, $D$3:D3, $E$3:E3, $E$3:E3, F$3:F3, $F$3:F3, $G$3:G3, $G$3:G3, $H$3:H3, $H$3:H3))-SUM(IF(COUNTIFS($D$3:$D$11, $D$3:D3, $E$3:$E$11, $E$3:E3, F$3:$F$11, $F$3:F3, $G$3:$G$11, $G$3:G3, $H$3:$H$11, $H$3:H3)=1, 1, 0))), remaining_formula)

becomes

IF(TRUE, "d"&(SUM(1/COUNTIFS($D$3:D3, $D$3:D3, $E$3:E3, $E$3:E3, F$3:F3, $F$3:F3, $G$3:G3, $G$3:G3, $H$3:H3, $H$3:H3))-SUM(IF(COUNTIFS($D$3:$D$11, $D$3:D3, $E$3:$E$11, $E$3:E3, F$3:$F$11, $F$3:F3, $G$3:$G$11, $G$3:G3, $H$3:$H$11, $H$3:H3)=1, 1, 0))), remaining_formula)

becomes

IF(TRUE, "d"&(SUM(1/1)-SUM(IF(COUNTIFS($D$3:$D$11, $D$3:D3, $E$3:$E$11, $E$3:E3, F$3:$F$11, $F$3:F3, $G$3:$G$11, $G$3:G3, $H$3:$H$11, $H$3:H3)=1, 1, 0))), remaining_formula)

becomes

IF(TRUE, "d"&(SUM(1/1)-SUM(IF(2=1, 1, 0))), remaining_formula)

becomes

IF(TRUE, "d"&(SUM(1/1)-SUM(IF(FALSE, 1, 0))), remaining_formula)

becomes

IF(TRUE, "d"&(1-0), remaining_formula)

becomes

IF(TRUE, "d"&1, remaining_formula)

and returns "d1" in cell B3.

Step 5 - Get value from the first duplicate record

This part of the formula is rund if the record is NOT the first duplicate of a specific record in the list, it finds the first record and returns that value.

In cell B4 the formula changes to the following line because of absolute and relative cell references.

INDEX($B$3:B4, MIN(IF(COUNTIFS(D4, $D$3:D4, E4, $E$3:E4, F4, $F$3:F4, G4, $G$3:G4, H4, $H$3:H4), MATCH(ROW($D$3:D4), ROW($D$3:D4)), ""))))

becomes

INDEX($B$3:B4, MIN(IF({1;1}, MATCH(ROW($D$3:D4), ROW($D$3:D4)), ""))))

becomes

INDEX($B$3:B4, MIN(IF({1;1}, {1; 2}, ""))))

becomes

INDEX($B$3:B4, MIN({1; 2}))

becomes

INDEX($B$3:B4, 1)

becomes

INDEX({"d1";"d1"}, 1)

and returns "d1" in cell B4.

The INDEX function returns a value based on a cell reference and column/row numbers.

12. Extract a list of alphabetically sorted duplicates based on a condition

Array formula in cell E5:

Back to top

12.1 How to enter an array formula

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

The formula now looks like this: {=arrayformula}

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

Back to top

12.2 Explaining formula in cell E5

You can easily follow along, select cell E5. Go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button.

Then press with left mouse button on "Evaluate" button to move to next step.

Step 1 - Check if category matches condition

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

becomes

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

becomes

COUNTIFS({"Winter"; "Winter"; "Winter"; "Summer"; "Winter"; "Summer"; "Winter"},"Winter",{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"},{"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"})>1

and returns

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

Step 2 - Check if count is larger than 1

This allows us to filter duplicate values.

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

becomes

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

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

Step 3 - Count previous values in list

COUNTIF($E$4:E4, $C$3:$C$9)

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

Step 4 - Check if they have not been shown before

This makes sure that previous values in column E are not repeated.

COUNTIF($E$4:E4, $C$3:$C$9)>0

becomes

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

and returns {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}

Step 5 - Multiply arrays

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

becomes

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

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

Step 6 - Build an array of sort ranking numbers

COUNTIF($C$3:$C$9, "<"&$C$3:$C$9) returns {2;4;2;0;5;0;5}

Step 7 - Return corresponding sort rank number

IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), "")

becomes

IF({1;0;1;0;1;0;1},{2;4;2;0;5;0;5}, "")

and returns {2;"";2;"";5;"";5}

Step 8 - Find the n-th smallest number

SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, ROWS($A$1:A1))

becomes

SMALL({2;"";2;"";5;"";5}, 1)

and returns 2.

Step 9 - Get the positions of values in the array

MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0)

becomes

MATCH(2, {2;4;2;0;5;0;5}, 0)

and returns 1.

Step 10 - Return value in data set based on coordinate

INDEX($C$3:$C$9, MATCH(SMALL(IF((COUNTIFS($B$3:$B$9, $F$2, $C$3:$C$9, $C$3:$C$9)>1)*(COUNTIF($E$4:E4, $C$3:$C$9)=0), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), ""), ROWS($A$1:A1)), COUNTIF($C$3:$C$9, "<"&$C$3:$C$9), 0))

becomes

INDEX($C$3:$C$9, 1)

becomes

INDEX({"Skates";"Ski";"Skates";"Fishing rod";"Sledge";"Fishing rod";"Sledge"}, 5)

and returns "Skates" in cell E5.

Back to top

13. Extract a list of alphabetically sorted duplicates based on a condition - Excel 365

Formula in cell F5:

Back to top

13.1 Explaining formula in cell F5

Step 1 - Check condition

The equal sign checks if the condition is equal to values in cell range B3:B9.

B3:B9=F2

becomes

{"Winter";"Winter";"Winter";"Summer";"Winter";"Summer";"Winter"}="Winter"

and returns

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

Step 2 - Filter values based on criteria

The FILTER function filters values in a given cell range based on a condition or criteria.

FILTER(C3:C9, B3:B9=F2)

becomes

FILTER({"Skates"; "Ski"; "Skates"; "Fishing rod"; "Sledge"; "Fishing rod"; "Sledge"}, {TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE})

and returns

{"Skates"; "Ski"; "Skates"; "Sledge"; "Sledge"}.

Step 3 - Assign a formula to a name

The LET function lets you assign a formula to a name. This reduces the formula size significantly.

FILTER(C3:C9, (B3:B9=F2) is assigned to z.

LET(z, FILTER(C3:C9, (B3:B9=F2)), formula)

Step 3 - Match filtered values

The MATCH function returns a number representing the relative position of an item in an array or cell range.

MATCH(z, z, 0)

becomes

MATCH({"Skates";"Ski";"Skates";"Sledge";"Sledge"}, {"Skates";"Ski";"Skates";"Sledge";"Sledge"}, 0)

and returns {1; 2; 1; 4; 4}.

Step 4 - Create a sequence based on the number of filtered values

The SEQUENCE function returns a sequence of numbers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROWS(z))

becomes

SEQUENCE(5)

and returns {1; 2; 3; 4; 5}.

Step 5 - Check for duplicates

The less than and greater than characters return TRUE if the numbers don't match indicating that the value is a duplicate.

MATCH(z, z, 0)<>SEQUENCE(ROWS(z))

becomes

{1; 2; 1; 4; 4}={1; 2; 3; 4; 5}

and returns {TRUE; TRUE; FALSE; TRUE; FALSE}.

Step 6 - Filter values

FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z)))

becomes

FILTER({"Skates"; "Ski"; "Skates"; "Sledge"; "Sledge"}, {TRUE; TRUE; FALSE; TRUE; FALSE})

and returns {"Skates";"Sledge"}.

Step 6 - Sort array

The SORT function sorts values in a cell range or array from A to Z.

SORT(FILTER(z, MATCH(z, z, 0)<>SEQUENCE(ROWS(z))))

becomes

SORT({"Skates";"Sledge"})

and returns {"Skates";"Sledge"}.

Back to top

Back to top

14. Extract duplicate values with exceptions

This example demonstrates a formula for earlier versions, Excel 2019, and previous versions. I recommend the smaller formula demonstrated in section 2 if you use Excel 365.

Array formula in cell F3:

Back to top

14.1 How to enter an array formula

1. Copy above array formula.
2. Double press on cell F3.
3. Paste formula to cell F3 (CTRL + v).
4. Press and hold CTRL + SHIFT simultaneously.
5. Press Enter once.
6. Release all keys.

The formula now begins with and ends with a curly bracket, this is Excel letting you know the formula is an array formula.

Don't enter these characters yourself, they appear automatically.

Back to top

14.2 Explaining the formula in cell F2

Step 1 - Count values

The COUNTIF function calculates the number of cells that is equal to a condition.

Function syntax: COUNTIF(range, criteria)

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

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak "; ... ;"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 - Check if the count number is larger than 1

The larger than sign is a logical operator, it returns TRUE or FALSE.

COUNTIF($B$3:$B$21, $B$3:$B$21)>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; ... ; FALSE}.

Step 3 - Check if previous results are not in $B$3:$B$21

$F$2:F2 is a cell reference to the cell above F3, it grows when the cell is copied to cells below. This makes it possible to keep track of previous results.

COUNTIF($F$2:F2, $B$3:$B$21)=0

becomes

COUNTIF("Duplicates",{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})=0

becomes

{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}

Step 4 - Count excluded values in $B$3:$B$21

COUNTIF($D$3:$D$4, $B$3:$B$21)<>1

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})<>1

becomes

{1; 0; 0; 0; 0; 0; 1; 0; 0; 1; 0; 1; 1; 0; 0; 0; 0; 0; 0}<>1

and returns

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

Step 5 - Multiply arrays - AND logic

The asterisk character lets you multiply numbers and boolean values in an Excel formula.

AND logic means that both conditions must be met, in other words, both arrays must contain TRUE in order to return TRUE.

TRUE * TRUE = TRUE (1)
TRUE * FALSE = FALSE (0)
FALSE * TRUE = FALSE (0)
FALSE * FALSE = FALSE (0)

When you multiply boolean values TRUE or FALSE the result is their numerical equivalent:
TRUE = 1
FALSE = 0 (zero)

(COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1)

becomes

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

and returns

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

Step 6 - Divide 1 by the array

The division character lets you divide numbers and boolean values in an Excel formula. Some numbers in the array above are 0 (zero) and some are 1. You can't divide a number by 0 (zero), Excel returns an error value.

However, the LOOKUP function ignores errors which we can take advantage of in this formula.

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

becomes

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

and returns

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

Step 7 - Return value

The LOOKUP function find a value in a cell range and return a corresponding value on the same row.

Function syntax: LOOKUP(lookup_value, lookup_vector, [result_vector])

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

becomes

LOOKUP(2, {#DIV/0!; #DIV/0!; #DIV/0!; 1; ... ; #DIV/0!}, {"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak "; ... ;"Almagro, Nicolas "})

and returns

"Wawrinka, Stanislas ".

Step 8 - Replace error values

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

The formula returns error values when it runs out of values, the IFERROR function displays nothing when that happens.

IFERROR(LOOKUP(2, 1/((COUNTIF($B$3:$B$21, $B$3:$B$21)>1)*(COUNTIF($F$2:F2, $B$3:$B$21)=0)*(COUNTIF($D$3:$D$4, $B$3:$B$21)<>1)), $B$3:$B$21), "")

Back to top

15. Extract duplicate values with exceptions - Excel 365

The following formula is a dynamic array formula and is entered as a regular formula, it works only in Excel 365.

Formula in cell F3:

Back to top

15.1 Explaining array formula in cell F2

Step 1 - Check if the value is a duplicate

The COUNTIF function counts values based on a condition. It can also be used to count multiple values.

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

becomes

COUNTIF({"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})>1

becomes

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

The larger than character checks if the numbers in the array is larger than 1. The output is TRUE or FALSE.

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

returns

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

Step 2 - Check if the value is in the exceptions list

This step returns an array that shows if the values are in the exceptions list or not.

COUNTIF(D3:D4, B3:B21)=0

becomes

COUNTIF({"Federer, Roger ";"Gonzalez, Fernando "},{"Federer, Roger ";"Djokovic, Novak ";"Murray, Andy "; ... ;"Almagro, Nicolas "})=0

and returns

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

Step 3 - Apply AND logic

The asterisk lets you multiply the arrays. It returns TRUE only if both arrays contain TRUE.

TRUE * TRUE = TRUE (1)
FALSE* TRUE = FALSE (0)
TRUE * FALSE= FALSE (0)
FALSE * FALSE= FALSE (0)

Note that the boolean values are converted into their numerical equivalents, TRUE = 1, and FALSE = 0 (zero).

(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)

becomes

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

and returns

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

Step 4 - Filter values

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

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0))

becomes

FILTER(B3:B21, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; "Murray, Andy "; ... ; "Almagro, Nicolas "}, {0; 0; 0; 1; 0; 0; 0; 1; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0})

and returns

{"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "}.

Step 5 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)))

becomes

UNIQUE({"Davydenko, Nikolay "; "Davydenko, Nikolay "; "Wawrinka, Stanislas "; "Wawrinka, Stanislas "})

and returns

{"Davydenko, Nikolay "; "Wawrinka, Stanislas "}.

Step 6 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

UNIQUE(FILTER(B3:B21,(COUNTIF(B3:B21,B3:B21)>1)*(COUNTIF(D3:D4,B3:B21)=0)))

x - B3:B21

LET(x,B3:B21,UNIQUE(FILTER(x,(COUNTIF(x,x)>1)*(COUNTIF(D3:D4,x)=0))))

Back to top

16. Get Excel *.xlsx file

17. Extract a list of duplicates from two or more columns combined - Excel 365

This example demonstrates how to list duplicates from three cell ranges combined, the first cell range is B3:B21, the second cell range is D3:D8 and the third is F3:F9.

Note, the cell ranges are not adjacent and are in different sizes. The result is an array shown in cell H3 and cells below as far as needed. This is called spilling in Excel 365.

Excel 365 dynamic array formula in cell H3:

You are not limited to three cell ranges, you can use up to 254 cell ranges.

Explaining the formula in cell H3

Step 1 - Combine cell ranges

The TOCOL function rearranges values in 2D cell ranges to a single column.

Function syntax: TOCOL(array, [ignore], [scan_by_col])

The TOCOL function lets you combine multiple cell ranges by adding parentheses and commas as delimiting characters.

TOCOL((B3:B21,D3:D8,F3:F9))

returns

{"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 "; "Djokovic, Novak "; "May Smith"; "Davydenko, Nikolay "; "Julia Ford"; "Del Potro, Juan Martin "; "Julia Ford"; "Wawrinka, Stanislas "; "Murray, Andy "; "Davydenko, Nikolay "; "Ruth, Arvid"; "Julia Ford"; "Ruth, Arvid"; "Penelope Swanson"}.

Step 2 - Match values to the same values

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(TOCOL((B3:B21,D3:D8,F3:F9)),TOCOL((B3:B21,D3:D8,F3:F9)),0)

becomes

MATCH({"Federer, Roger "; "Djokovic, Novak "; ... ; "Penelope Swanson"}, {"Federer, Roger "; "Djokovic, Novak "; ... ; "Penelope Swanson"}, 0)

and returns

{1; 2; 3; 4; 5; 6; 1; 4; 9; 10; 11; 10; 13; 14; 15; 11; 17; 18; 19; 2; 21; 4; 23; 6; 23; 11; 3; 4; 29; 23; 29; 32}.

Step 3 - Count rows in the array

The ROWS function calculate the number of rows in a cell range.

Function syntax: ROWS(array)

ROWS(TOCOL((B3:B21,D3:D8,F3:F9)))

becomes

ROWS({"Federer, Roger "; "Djokovic, Novak "; ... ; "Penelope Swanson"})

and returns

32. There are 32 rows (values) in the array.

Step 4 - Create a sequence from 1 to n

The SEQUENCE function creates a list of sequential numbers.

Function syntax: SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(ROWS(TOCOL((B3:B21,D3:D8,F3:F9))))

becomes

SEQUENCE(32)

and returns

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32}.

Step 5 - Check if the numbers are not equal to the sequence

The less than and larger than sign combined lets you perform "not equal to" in an Excel formula. The result is a boolean value TRUE or FALSE, however, in this case, the result is an array containing boolean values.

MATCH(TOCOL((B3:B21,D3:D8,F3:F9)),TOCOL((B3:B21,D3:D8,F3:F9)),0)<>SEQUENCE(ROWS(TOCOL((B3:B21,D3:D8,F3:F9))))

becomes

{1; 2; 3; 4; 5; 6; 1; 4; 9; 10; 11; 10; 13; 14; 15; 11; 17; 18; 19; 2; 21; 4; 23; 6; 23; 11; 3; 4; 29; 23; 29; 32}<>{1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32}

and returns

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

This array lets you identify duplicate values, their positions correspond to the position in cell ranges B3:B21, D3:D8, and F3:F9.

Step 6 - Filter values based on the boolean array

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

Function syntax: FILTER(array, include, [if_empty])

FILTER(TOCOL((B3:B21,D3:D8,F3:F9)),MATCH(TOCOL((B3:B21,D3:D8,F3:F9)),TOCOL((B3:B21,D3:D8,F3:F9)),0)<>SEQUENCE(ROWS(TOCOL((B3:B21,D3:D8,F3:F9)))))

becomes

FILTER({"Federer, Roger "; "Djokovic, Novak "; ... ; "Penelope Swanson"},{FALSE; FALSE; FALSE; ... ; FALSE})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Djokovic, Novak "; "Davydenko, Nikolay "; "Del Potro, Juan Martin "; "Julia Ford"; "Wawrinka, Stanislas "; "Murray, Andy "; "Davydenko, Nikolay "; "Julia Ford"; "Ruth, Arvid"}.

Step 7 - Extract unique distinct values

The UNIQUE function returns a unique or unique distinct list.

Function syntax: UNIQUE(array,[by_col],[exactly_once])

UNIQUE(FILTER(TOCOL((B3:B21,D3:D8,F3:F9)),MATCH(TOCOL((B3:B21,D3:D8,F3:F9)),TOCOL((B3:B21,D3:D8,F3:F9)),0)<>SEQUENCE(ROWS(TOCOL((B3:B21,D3:D8,F3:F9))))))

becomes

UNIQUE({"Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "... ; "Ruth, Arvid"})

and returns

{"Federer, Roger "; "Davydenko, Nikolay "; "Gonzalez, Fernando "; "Wawrinka, Stanislas "; "Djokovic, Novak "; "Del Potro, Juan Martin "; "Julia Ford"; "Murray, Andy "; "Ruth, Arvid"}

Step 8 - Shorten the formula

The LET function lets you name intermediate calculation results which can shorten formulas considerably and improve performance.

Function syntax: LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

UNIQUE(FILTER(TOCOL((B3:B21,D3:D8,F3:F9)),MATCH(TOCOL((B3:B21,D3:D8,F3:F9)),TOCOL((B3:B21,D3:D8,F3:F9)),0)<>SEQUENCE(ROWS(TOCOL((B3:B21,D3:D8,F3:F9))))))

x - TOCOL((B3:B21,D3:D8,F3:F9))

LET(x,TOCOL((B3:B21,D3:D8,F3:F9)),UNIQUE(FILTER(x,MATCH(x,x,0)<>SEQUENCE(ROWS(x)))))

18. Extract a list of duplicates from three columns combined

The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is displayed in cell H3 and cells below.

Formula in cell H3:

Explaining formula in cell F3

This formula consists of three similar parts, one returns values from List1 and the second returns values from List2 and the third returns duplicates from List3.

IFERROR(IFERROR(formula1, formula2), formula3)

Step 1 - Prevent duplicate values in output

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 not returned.

COUNTIF($H$2:H2,$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 in List1.

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($H$2:H2,$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($H$2:H2,$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($H$2:H2,$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, 1/{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 H3.

Step 4 - 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.

Another IFERROR function is used to handle errors from List2, the formula then returns values from List.

IFERROR(IFERROR(formula1, formula2), formula3)

Get Excel *.xlsx file

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

 

Duplicate values category

More than 1300 Excel formulas

Excel categories

Comments (62)