Join Collect multiple columns from another sheet

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