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)

Duplicate values category

More than 1300 Excel formulas

Excel categories

Comments (59)