Extract unique distinct values based on a date range
Answer:
The date range is specified in cell G1 and G2, see image above.
Array formula in cell D2:
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.
Explaining the formula in cell D2
Step 1 - Filter numbers in date range
IF(($G$2>=$B$2:$B$17)*($G$1<=$B$2:$B$17),$A$2:$A$17,$D$1)
becomes
IF((39833>={39825; 39840; 39818; 39836; 39817; 39830; 39832; 39831; 39840; 39836; 39840; 39821; 39840; 39832; 39829; 39823})*(39823<={39825; 39840; 39818; 39836; 39817; 39830; 39832; 39831; 39840; 39836; 39840; 39821; 39840; 39832; 39829; 39823}),{12; 77; 42; 12; 19; 77; 17; 7; 12; 19; 11; 17; 2; 12; 12; 77},"Unique distinct list")
and returns
{12; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; 77; 17; 7; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; 12; 12; 77}
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 2 - Check numbers already displayed
COUNTIF($D$1:D1,IF(($G$2>=$B$2:$B$17)*($G$1<=$B$2:$B$17),$A$2:$A$17,$D$1))
becomes
COUNTIF($D$1:D1,{12; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; 77; 17; 7; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; "Unique distinct list"; 12; 12; 77})
and returns
{0; 1; 1; 1; 1; 0; 0; 0; 1; 1; 1; 1; 1; 0; 0; 0}
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
How to use the COUNTIF function
Step 3 - Find position of next unique distinct number
MATCH(0,COUNTIF($D$1:D1,IF(($G$2>=$B$2:$B$17)*($G$1<=$B$2:$B$17),$A$2:$A$17,$D$1)),0)
becomes
MATCH(0,{0; 1; 1; 1; 1; 0; 0; 0; 1; 1; 1; 1; 1; 0; 0; 0},0)
and returns 1.
Identify the position of a value in an array.
Step 4 - Return number
INDEX($A$2:$A$17,MATCH(0,COUNTIF($D$1:D1,IF(($G$2>=$B$2:$B$17)*($G$1<=$B$2:$B$17),$A$2:$A$17,$D$1)),0))
becomes
INDEX($A$2:$A$17,1)
becomes
INDEX({12; 77; 42; 12; 19; 77; 17; 7; 12; 19; 11; 17; 2; 12; 12; 77},1)
and returns 12 in cell D2.
Gets a value in a specific cell range based on a row and column number.
Download Excel file

unique-list-to-be-created-from-a-column-where-an-adjacent-column-is-in-a-date-range.xlsx
Recommended reading:
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Vlookup – Return multiple unique distinct values
Unique distinct list sorted alphabetically based on a condition
Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users. =SORT(UNIQUE(FILTER(C3:C11, E3=B3:B11))) […]
Unique distinct list sorted alphabetically based on a condition
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 […]
Find latest date based on a condition
Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]
Formula for matching a date within a date range
Table of contents Match a date when a date range is entered in a single cell Match a date when […]
The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]
Identify overlapping date ranges
The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Extract a unique distinct list sorted from A to Z
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
4 Responses to “Extract unique distinct values based on a date range”
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 Oscar,
Thanks for sharing the knowledge. I've a query in case of two columns of dates and two columns of data.
Calling columns A and B as data(text), C and D as Dates(dd/mm/yy), IF column D's date is not empty and matches to the range, concatenate A & B and paste in in different sheet, ELSE column C's date(column C will always have a date unlike column D) should be taken for range calculation and then concatenate A & B cells and paste in a cell.
Please do help in regards to this query.
Best Regards,
Krish
krish,
read this post:
Sort cell values in corresponding columns
I've created a new thread for this in a forum, which has got clear explanation. It'd be great to hear from you.
https://www.excelforum.com/excel-programming-vba-macros/901407-if-condition-two-cells-with-dates-and-concatenation-of-corresponding-2-text-cells.html
Many Thanks.
Hi Oscar, your formulas are absolute legend.
Any advice on using this formula with an extra criteria.
ie. create a unique list between 2 dates and matching a certain criteria from column C
Thanks in advanced.
Mike