## Filter duplicate values and sort by corresponding date

**Array formula in D2:**

**Array formula 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

### Dates category

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]

This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]

The image above demonstratesÂ an array formula in cell E4 that searches for the closest date in column A to the […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

This article demonstrates formulas that show if a date range is overlapping another date range.Â The second section shows how […]

This article demonstrates a formula that extracts unique distinct records/rows for a given month specified in cell C2, see the […]

This article explains how to find the smallest and largest value using two conditions. In this case they are date […]

I found an old post that I think is interesting to write about today. Think of two overlapping ranges, it […]

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

The array formula in cell D4 extracts the start dates for date ranges in cell range B3:B30, the array formula […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]

Table of Contents Filter unique distinct values based on a date range How to enter an array formula Filter unique […]

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]

Question: How to create unique distinct year and months from a long date listing (column A)? You can find the […]

In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]

The array formula in cell B3 creates a list of dates based on the date ranges displayed in D3:E7, it […]

This article demonstrates a formula that returns a date range that a date falls under, cell C3 above contains the […]

This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]

### Duplicate values category

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

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 […]

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

The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]

Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]

This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]

The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]

The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]

This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]

This article demonstrates formulas and Excel tools that extract duplicates based on three conditions. The first and second condition is […]

Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]

## Functions in this article

More than 1300 Excel formulas

## Excel formula categories

## Excel categories

### One Response to “Filter duplicate values and sort by corresponding date”

### 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 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!