Author: Oscar Cronquist Article last updated on January 03, 2019

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

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

Answer:

The date range is specified in cell G1 and G2, see image above.

Array formula in cell D2:

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

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}

How to use the IF function

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

How to use the IF function

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.

How to use the MATCH function

Identify the position of a value in an array.

How to use the MATCH function

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.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the INDEX function

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

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

Unique distinct list sorted alphabetically based on a condition

Related articles

Extract unique distinct year and months from dates

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

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to [โ€ฆ]