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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Are you able to provide some sample screenshots for context?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
I see it now. thank you!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!