Using Join(Collect to collect data from multiple sheets to populate in (1) cell

DB393

I am looking to combine data from (3) sheets into one sheet.

There will be 3 columns: Team, Assignment, and Notes

The cells in the Team and Assignment columns from Sheet1, Sheet2 and Sheet3 are populated from a multi-select dropdown lists.

Notes are free type.

Example: p-id on the destination sheet is xx, Sheet1 and Sheet2 have p-id's with xx, Sheet3 does not.

Sheet1 has Team A and Team B

Sheet2 has Team A, Team B and Team C

My desired result in the destination sheet would be Team A, Team B, Team C combined together in the cell removing the duplicate team names.

Same logic would apply for the formula pulling the Assignments.

I am not sure if the Join(Collect formula would work for the Notes requirements.

TIA for your assistance/suggestions!

Paul Newcome

No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.

Paul Newcome

Are you able to provide some sample screenshots for context?

DB393

Hey @Paul Newcome, I think what I need is the ability to combine multiple join(distinct(collect formulas.

I created a helper sheet to consolidate one of the sheets that was about 80% of the lift. =JOIN(DISTINCT(COLLECT({sift}, {sifd}, [Prospect-ID]@row, {msift}, {msifd}, [Prospect-ID]@row)), CHAR(75))

this formula works just fine, however I need aggregate the data from the other (2) sheets

I tried:

=JOIN(DISTINCT(COLLECT({sift}, {sifd}, [Prospect-ID]@row, {msift}, {msifd}, [Prospect-ID]@row)), CHAR(75)) + JOIN(DISTINCT(COLLECT({msift}, {msifd}, [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({tsift}, {tsifd}, [Prospect-ID]@row)), CHAR(75))

But I got an incorrect argument error. I Am I supposed to nest them all under the collect criteria?

Thank you for your help!

Paul Newcome

It looks like your first COLLECT is off.

You have range, range, criteria, range, range, criteria.

DB393

I thought it was range, critrange, criteria?

Wouln't the Critrange have to reference the source sheet?

Or do I need to change it to {Range},[ColumnName]@row,[criteria]@row.. then "+" {Range},[ColumnName]@row,[criteria]@row....etc?

DB393

=JOIN(DISTINCT(COLLECT({sift}, [Prospect-ID]:[Prospect-ID], [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({msift}, [Prospect-ID], [Prospect-ID]@row)), CHAR(75))+JOIN(DISTINCT(COLLECT({tsift}, [Prospect-ID], [Prospect-ID]@row)), CHAR(75))

Unparseable error

Paul Newcome

No. In your formula from your post just before my last one, you were adding together 3 different joins. The first one has a different syntax from the 2nd and 3rd. The 2nd and 3rd have the correct syntax already.

DB393

I see it now. thank you!

Paul Newcome

Happy to help. 👍️