Concatenate Names Based on Two Factors

TracyS

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?

Antonie B

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!

TracyS

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!