Join Collect multiple columns from another sheet

heyjay

I have this growing reference sheet with Site_Name and respective emails (1-3). Not all of them have all 3 emails. Please also assume that the emails are not beside each other. They could be 2-3 columns apart.

I need to merge their emails on this order tracker, but not sure how to use Join Collect if there are multiple columns.

ericncarr

Not sure you will want to go this route, but the way I would personally do this is to list each Site/Email separately, so you would just have 2 columns, Site & Email.

Then the join collect would work pretty easily:

The formula would be straightforward enough too, just set your Company Email Merged to be a Multiple Select Dropdown column.

=JOIN(COLLECT({Email column reference}, {Site Column reference}, =Site@row), CHAR(10))

heyjay

https://community.smartsheet.com/discussion/comment/343414#Comment_343414

The sheet has already been established and we cant move or change the it since the three emails are designated to a level in the organization. thanks for trying though.

ericncarr

@heyjay gotcha, worth a try.

Noodled a bit, here's two ways to do it then but both will require helper columns. For any email columns you want to collect, create a helper column with the formula =[email 1], another with =[email 2] and so forth. This way you get the emails in a range next to each other. Once you have it set up, you can hide these columns.

If you want it exactly like you have it above, the values separated by ";", then try this formula.

=JOIN(COLLECT([Email 1]@row:[Email 3]@row, [Email 1]@row:[Email 3]@row, NOT(ISBLANK(@cell))), "; ")

Of course, make the range the helper columns. This collects any values in that range that are not blank then joins them together with a "; " separating them.

The other option is to do this, making the "Desired Result" column a multi-select dropdown. Just depends what you're doing with the data, if you're parsing it or something use the first option.

=JOIN([Email 1]@row:[Email 3]@row, CHAR(10))

Both options side by side