Concatenate Names Based on Two Factors
Hello,
I'm trying to pull a list of names from a sheet based on two factors. In sheet one I need to match if a value is in a column, to use the vendor name from the column and match the vendor name in sheet 2, to pull every name assigned.
Sheet 1: If Needs Review is greater than 0, then find vendor name, and match to vendor name in sheet 2....
Sheet 2: Match Vendor Name from sheet 1, and return all names from Requested By. Although I changed the names, a vendor may have multiple names who requested information, which I need to put into a single column.
I've tried a few different ways from Collect Distinct, to Collect IFS. Right now I used a formula I found, and although I know it is not right. This is my most recent attempt:
=IF(COLLECT([Needs Review]@row <> 0, AND({Vendor}, Vendor@row, {Requested By} + {Requested By}))
What would be the right way?
Answers
-
Hi @TracyS -
It sounds like you want to use a Join/Distinct/Collect formula, that is used with an IF statement. This will pull all the unique assignees, but will skip duplicates while accommodating your parameters.
Try this: =IF([needs review]@row > 0, (JOIN(DISTINCT(COLLECT({Range for Requested by}, {Range for Vendor name}, Vendor@row)), CHAR(10))), "No review needed")
I also use the CHAR function to add a line break between requesters but you can add whatever delimiter you'd like
If you want to read up on the functions, you can have a look here:
IF Function: https://help.smartsheet.com/function/if
Join Function: https://help.smartsheet.com/function/join
Distinct Function: https://help.smartsheet.com/function/distinct
Collect Function: https://help.smartsheet.com/function/collect
Char function: https://help.smartsheet.com/function/char
Hope this helps!
-
Perfect that helped and was exactly what I needed. I wasn't thinking of Collect, I'll read a bit more but I do see what you did with this one. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K 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
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!