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!)