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 […]
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 […]
Functions in this article
More than 1300 Excel formulas
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.
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!