Home > Software design >  TEXTJOIN string only for values matching Todays date
TEXTJOIN string only for values matching Todays date

Time:05-13

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

CodePudding user response:

use:

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

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

CodePudding user response:

Try the following:

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

Ironically, the solution includes FILTER, which your text portion contains.

(Jinx, player0!)

  • Related