JOIN and COLLECT

System

This discussion was created from comments split from: dropdown multi-select and lookups -- vlookup, join, collect, index, match???.

Jeanne

Hi All:

I am trying to do something very similar, but seems that I am missing something or have too much - I keep getting #UNPARSEABLE error.

I have 2 fields in my Sheet 1: "Lead" is single select, and "Additional Contributors" is multi-select. I was using VLOOKUP to get the team from Sheet 2 (column 2 in that sheet) when I was only requiring the team of the LEAD. Now trying to use JOIN and COLLECT to also obtain the teams for each of the additional contributors, and concatenate it into the same cell in Sheet 1. This is the formula I have been trying to get to work:

=JOIN(COLLECT(Lead@row),{Staff List Range 3}, 2, ", ") + ", " + JOIN(COLLECT([Additional

Contributors]@row, {Staff List Range 3}, 2 ", "))

Any help would be greatly appreciated as I am on a deadline to make this work.

Thank you.

Paul Newcome

Are you able to provide some screenshots for reference?

Jeanne

Hi Paul:

Currently, I pull the Therapeutic Area/Team using a Vlookup on another sheet (Staff List) on the "Lead" (single select field) name. Now I would either like to add the Therapeutic Area/Team from the Additional Contributors field (multi-select field) to the same Therapeutic Area/Team field OR at the very least, add the TA/Team for each of the additional contributors to a new field.

Jeanne

Any additional thoughts?

Genevieve P.

Hi @Jeanne

The COLLECT function needs the first thing listed to be the RANGE that you want to pull back information from. Then you list each column and criteria after it, like so:

COLLECT({Column to Return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2")

So actually in your instance it sounds more like you want to add together two separate VLOOKUP formulas returning different values.

For your Additional Contributors column, how does the source sheet have these values listed? There currently isn't a way to look into a multi-select cell and search for individual parsed values in a separate sheet. The source sheet would need to have the same combinations of users in one cell in order to find a match. Does that make sense?

Jeanne

Hi Genevieve:

Yes, that does make sense - I was concerned that there was not a way possible to take the individual contact names from the multi-select field and lookup each one individually to see what team they belonged to in the 'Staff List' sheet. In the "Additional Contributors" column, the contacts could be from several different teams, all one team, or any combination of teams.

I appreciate you getting back to me and letting me know. I originally had a field on the entry sheet to manually select the teams of the additional contributors and that seems like it is going to be the way I have to go.

Thank you. I considered this question resolved.