Help with JOIN(DISTINCT(COLLECT formula
I'm currently using this formula: JOIN(DISTINCT(COLLECT({Training}, {Identifier}, [Loc - Employee]@row)), CHAR(10)) in order to pull completed training records for a list of employees. The idea is that if a specific employee has multiple training records, I can use this formula to return these records to compare against their assigned training requirements on another sheet. It works exactly as intended when the training records exist on one row with only one employee being trained. But fails to return anything when there are multiple employees on one training record. Ideally I would like the training record to be pulled so long as the Criterion_Range1 (in my case the {Identifier}) contains the Criterion (in my case [Loc - Employee]@row), regardless if there is another employee within the same cell. Either what I'm asking for is not possible, or I simply can't get my head around creating the proper syntax for that. Examples below:
Training Assigned:
Training Records:
Note that in training record Row 5, Joe and Jorge received Safe Lifting training together but the record was not pulled into the Trainings Completed.
Best Answer
-
Hi @Alexis R
Try modifying your formula as below
JOIN(DISTINCT(COLLECT({Training}, {Identifier}, CONTAINS([Loc - Employee]@row, @cell))), CHAR(10))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
Hi @Alexis R
Try modifying your formula as below
JOIN(DISTINCT(COLLECT({Training}, {Identifier}, CONTAINS([Loc - Employee]@row, @cell))), CHAR(10))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hi @AravindGP,
This worked, thank you!
I'll be honest, I tried inserting the CONTAINS function there before but couldn't get it to work. I'll have to assume it has something to do with the "@cell" that you chose for the "search_within" parameter. If you don't mind me asking, what exactly does "@cell" do and why does only that work here but not if I try to use {Identifier} again as the "search_within"?
-
Hi @Alexis R
@cell is looking for a value within the cells in a defined range. Since we are already referencing the range in the collect statement, we do not need to reference the range again.
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 464 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!