JOIN COLLECT with two criteria

kstrobush

I have a sheet full of data from another source and trying to pull some of its contents into my main sheet.

The data sheet has a row for each speaker associated with a session and their role. So the sheet may have several rows with the same session title if there are multiple speakers.

The destination sheet has one row for each session and I want to collect any/all the Role 1 speakers into one column (multi-select dropdown), Role 2 in the next, and so on.

What I'm trying to make happen in the destination sheet:

I have a formula that will collect ALL of the Role 1/2/3 names into a single cell,

JOIN(COLLECT({Source Sheet Speaker Name}, {Source Sheet Role#}, =1), CHAR(10))

I can not figure out how to add in the extra layer doing the JOIN/COLLECT for cells from the source sheet only where it has a Title match.

Hollie205

Had a typo in my formula

=JOIN(COLLECT({Source Sheet Speaker Name},{Source Sheet Role#},=1,{Source Sheet Title},Title@row), CHAR(10))

It was missing a bracket and it should be your destination sheet title@row. Make sure all of your references are created correctly. I did a couple of fake sheets and set them up as you have above so it should work.

Hollie205

Try

=JOIN(COLLECT({Source Sheet Speaker Name,{Source Sheet Role#},=1,{Source Sheet Title},[Reference Sheet Title]@row), CHAR(10))

kstrobush

Hi @Hollie205 , thank you for taking a look!

That solution is currently returning an #INCORRECT ARGUMENT SET for me.

Hollie205

Had a typo in my formula

=JOIN(COLLECT({Source Sheet Speaker Name},{Source Sheet Role#},=1,{Source Sheet Title},Title@row), CHAR(10))

It was missing a bracket and it should be your destination sheet title@row. Make sure all of your references are created correctly. I did a couple of fake sheets and set them up as you have above so it should work.

kstrobush

Those edits did it! Thank you!