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

Best Answer

  • Deric
    Deric ✭✭✭✭✭
    edited 02/28/24 Answer ✓

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!