Search code examples
google-sheetsfiltergoogle-sheets-formulaunique

How can I list unique values within a date range?

In sheet 1 I have a list of project codes in column A and dates in column B.

In sheet 2, I'm trying to generate a list from sheet 1 of unique project codes from column A within certain dates from column B. I would like to be able to change this date range easily based on the date range in sheet 2 between cell D2 and E2.

enter image description here

enter image description here

I have tried the following formula and have not been able to get the result I need:

=if(">="&$D$2,"<="&$E$2,unique('Sheet 1'!$A$2:$A))

This returns the following:

enter image description here

and this:

=UNIQUE(FILTER('Sheet 1'!$A$2:$A,'Sheet 1'!$B$2:$B,">="&$D$2,'Sheet 1'!$B$2:$B,">="&$E$2))

which returns this:

enter image description here

Can anyone help?

Edit:

I have tried using the formula:

=UNIQUE(FILTER('Sheet 1'!A2:A,'Sheet 1'!B2:B>=D2,'Sheet 1'!B2:B>=E2))

This appeared to work initially however when I added additional project codes to column A in sheet 1 these did not appear.

I edited the formula to:

=UNIQUE(FILTER('Sheet 1'!A2:A,'Sheet 1'!B2:B>=1/1/2023,'Sheet 1'!B2:B>=30/1/2023))

This gave the results I was looking for however I would really like to be able to use date ranges that I can change in cells D2 and E2 in sheet 2 in the formula rather than dates.

Any suggestions?

Solution

  • Can you try:

    =UNIQUE(FILTER('Sheet 1'!A2:A,'Sheet 1'!B2:B>=D2,'Sheet 1'!B2:B<=E2))