Search code examples
google-sheetsfilterconcatenationgoogle-sheets-formulatextjoin

TEXTJOIN string only for values matching Todays date

Sample Table

Date Created (A) Tracking ID (B)
5/12/2022 '813vz633-7872'
5/12/2022 '914bz756-7423'
5/12/2022 '875vu340-5687'
5/11/2022 '475dv235-6542'
5/11/2022 '905tg183-9634'

I want to create a string that looks like this:

filter message.trackingId not in ['813vz633-7872', '914bz756-7423', '875vu340-5687']

This is achieved by using formula:

="filter message.trackingId not in ["&TEXTJOIN(", ", TRUE, B2:B4)&"]"

But I want it to automatically pull from Column B only if Column A is todays date

I tried:

="filter message.trackingId not in ["&TEXTJOIN(", ", TRUE, IF(A:A=TODAY(),TEXTJOIN("", TRUE,B2:B))&"]")

But that pulls in all values in Column B, not just for todays date

I know I am close, just need a push

Solution

  • use:

    =TEXTJOIN(", ", 1, FILTER(B:B, A:A=TODAY()))

    ="filter message.trackingId not in ["&TEXTJOIN(", ", 1, FILTER(B:B, A:A=TODAY()))&"]"