Using Join(Collect to collect data from multiple sheets to populate in (1) cell
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!
Best Answer
-
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.
Answers
-
Are you able to provide some sample screenshots for context?
-
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!
-
It looks like your first COLLECT is off.
You have range, range, criteria, range, range, criteria.
-
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?
-
=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
-
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.
-
I see it now. thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 150 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!