How can i find DISTINCT names from multiple JOIN(Distinct(Collect)
I am collecting instructor names for courses from multiple sheets which works fine, but it may list the same person multiple times as it finds them in each sheet.
=JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccsp23}, {cnsp23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccfa22}, {F22CN}, Course@row)))
Is it possible to wrap the searches below around DISTINCT to show one unique name? i.e. =DISTINCT(JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccsp23}, {cnsp23}, Course@row))) + JOIN(DISTINCT(COLLECT({ccfa22}, {F22CN}, Course@row))))
Answers
-
Hi @gwson,
You could try:
=JOIN(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row) + COLLECT({ccsp23}, {cnsp23}, Course@row) + COLLECT({ccfa22}, {F22CN}, Course@row)))
Regards
J Tech
If my response has helped you in any way or provided a solution to your problem, please consider supporting the community by marking it as Insightful, Vote Up, or Awesome. Additionally, you can mark it as the accepted answer, which will make it easier for others to find a solution or get help with a similar issue in the future. Your support is greatly appreciated!
-
I get an invalid operation error.
-
@gwson unfortunately you can't concentrate ranges, so you'll have to create a larger range first.
I did this for a client recently. I'll work a demo up and do a video. Stay tuned!
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
My suggestion is to use some helper columns to parse each distinct list into their own column then run a JOIN/DISTINCT on these helper columns.
First insert a text/number column (called "Number" in this example) with numbers manually entered starting with 1 and going down as far as you need.
1
2
3
so on and so forth.
Then in the next column you would use an INDEX/DISTINCT/COLLECT and reference the Number column.
=IFERROR(INDEX(DISTINCT(COLLECT({ccsu23}, {cnsu23}, Course@row)), Number@row), "")
You would then duplicate this for the next column over pulling in your second list and then again in another column to get your third list.
Then your JOIN would look more like:
=JOIN(DISTINCT(COLLECT([Helper 1]:[Helper 3], [Helper 1]:[Helper 3], @cell <> "")), "delimiter of choice")
-
@gwson @Paul Newcome Yes, what Paul said is the way. Also, you may need to filter out blank cells when you collect over that range of helper cells, but that is easy using not(isblank(@cell)) as a criterion in the collect function.
I did make a video inspired by your question, so you can see it in action. I don't show using the JOIN() function, but otherwise, you'll do the same thing and it also shows about using not(isblank(@cell)) as a criterion in the collect function.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Darren Mullen Mine does exclude blanks. Thats where the <> "" piece comes into play.
I prefer that over NOT(ISBLANK(@cell)) because not only is it less work my fingers have to do (haha), but it is also less parenthesis to accidentally misplace.
-
@Paul Newcome Ahh, I missed that in your formula :)
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 443 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!