Question: I have a large list of dates and other adjacent values. I want to create a distinct list from the large list with a criteria, only values within a specific date interval.

create-a-unique-distinct-list-from-a-date-range2

Answer:

The date range is specified in cell G1 and G2.

create-a-unique-distinct-list-from-a-date-range

Here is the formula:

=IFERROR(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)), "")

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

Unique distinct list sorted alphabetically and based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Explaining the formula

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}

IF function explained

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

Learn to use the COUNTIF function

Counts the number of times a value exists in a cell range.

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.

How to use Excel’s MATCH function

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.

INDEX function explained

Fetch a value in a data set based on coordinates.

Download excel example file
unique-list-to-be-created-from-a-column-where-an-adjacent-column-is-in-a-date-range.xlsx
(Excel 2007-2016 Workbook *.xlsx)