Joining two data sets using a multiselect column

JLC

Hoping for either formula help or suggestions for how to restructure the data so that I can make this happen! Please let me know if I can clarify anything and thanks in advance to anyone willing to take a stab at this.

Main sheet: list of events; one row per event, with the following columns:

1. Event name (text)
2. Attendee names (multi-select dropdown)
3. Departments represented at event (this is what I need help formulating)

Data sheet: list of all attendees' respective departments; one row per attendee, with the following columns:

1. Attendee name (text)
2. Attendee department (text)

Example of desired output:

1. Event A
2. Attendee 1, Attendee 2, Attendee 3
3. Attendee 1's department, Attendee 2's department, Attendee 3's department

How can I connect these two sheets so that I can get either lists or counts of the departments present at each event? Alternatively, I am also okay with having column #3 be something like "Department A present?", column #4 be "Department B present?", column #5 be "Department C present?", etc.

Some thoughts I had, whether they'd help or not, include: having one data sheet of attendees per department, renaming all attendees to include their department in their name

Deric

This is what i came up with: =JOIN(DISTINCT(COLLECT({Data_AttendeeDepartment}, {Data_AttendeeName}, HAS([Attendee names]@row, @cell))), CHAR(10))

MichaelTCA

Hello @JLC

Have you tried the JOIN/COLLECT function?

On your data sheet you will need to specify which event each individual person is going to as well.

It will be a combination of a few JOIN and JOIN/COLLECT functions to get you the visual that you are looking for.

Deric

This is what i came up with: =JOIN(DISTINCT(COLLECT({Data_AttendeeDepartment}, {Data_AttendeeName}, HAS([Attendee names]@row, @cell))), CHAR(10))

JLC

@Deric this is PRECISELY what I was looking for. Thank you so very much!!!