CODEWITHSUNDEEP
regexgoogle-sheetsfilterarray-formulastextjoin
Joseph Schleiss
Joseph Schleiss

Reputation: 11

Returning only the values that occured more than a certain date range ago

I have a spreadsheet where Column A is the year, and column B is the shirt color used. The shirt colors are repeated.

I want to find a way to generate a list of the colors that have not been used in the last 10 years. The problem I am running into is due to the fact that the colors repeat. I tried using 

=unique(filter(B2:B, A2:A<today()-(365*10)))

but shirts that were used in the last 10 years are then still included.

Upvotes: 1

Views: 27

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TEXTJOIN(", ", 1, UNIQUE(IF(NOT(REGEXMATCH(B:B, 
 TEXTJOIN("|", 1, UNIQUE(FILTER(B:B, A:A>=YEAR(TODAY())-10))))), B:B, ))))

enter image description here

for dates in column A use:

=ARRAYFORMULA(TEXTJOIN(", ", 1, UNIQUE(IF(NOT(REGEXMATCH(B:B, 
 TEXTJOIN("|", 1, UNIQUE(FILTER(B:B, YEAR(A:A)>=YEAR(TODAY())-10))))), B:B, ))))

10

Upvotes: 1

Related Questions