Consolidate unique values from multiple columns into single column
Hi all, I was curious to know if there was a way to extract the unique values from columns and consolidate it into a single column?
For some context, we have individuals under different columns based on their position and location, similar to this screenshot (location through manager)
The roster I actually have on a different sheet. What I want to do is find a way so that as people are added, changed or removed - the roster would update so that we can have a full list of everyone across all of the locations. Anyone have any thoughts on how to tackle this issue?
Best Answer
-
You would need to use a JOIN/DISTINCT combo to generate a string of all distinct names then use a parsing solution. Here is a thread with more details...
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
-
Hello @kioshi43
Thank you for your post! May you clarify what the ideal end result would look like? When you mean consolidate? Are you consolidating based on similar criteria into 1 column? Are you just consolidating "Roster", "Location", "Coordinator", "Assistant Manager", "Manager" into another column? May you provide more details on this!
You do have the options to use a formula or automation to copy/move to/from another sheet's 1 column as people are added, changed or removed.
Cheers~
Krissia
-
Appreciate you responding!
The ideal end state is to have all of the unique names within the Coordinator, Assistant Manager, and Manager columns appear in a single Roster column (which is in a separate sheet). This would make it easier to identify who is on the team, no matter how often their names appear in the location assignments. I tried looking at ways to use a formula to consolidate the three columns into a single column but didn't have much success.
-
You would need to use a JOIN/DISTINCT combo to generate a string of all distinct names then use a parsing solution. Here is a thread with more details...
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!
-
@Paul Newcome The link to your parsing solution is dead. I would very much like to learn how to do what you describe: pull a string into a sheet and parse it out down a column.
I would be grateful for either a description of the functions used, or an example that I could crib from.
Best regards,
Adam
-
Hey @Adam Wasserman
I've looked through the forum but haven't found a detailed description of how to do this, and I'm actually not sure. Once something is in a string I personally have a difficult time parsing it out.
Here's another comment where Paul explains it a bit:
I believe it will have something to do with using LEFT to grab all the data to the left of the delimiter.
@Paul Newcome any chance that sheet still exists and the Publish function was just turned off?
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
I turned off the publishing because there was another more efficient solution presented by someone else that uses the SUBSTITUTE function and adjusting the placement of the SUBSTITUTE to essentially place a locator. I must have forgotten to include a link to that thread when I did that. I can't remember who posted it though, but I have tweaked it a bit.
First you will need a text number column (called "Number" in this example). In this column you would manually enter the numbers 1 through however many you will need.
Then in the column you want to parse the list down, you would use something like this:
NOTE: This is under the assumption that you will not have ! (exclamation point) or | (pipe) in any of the data you are wanting to pull through. If you are, then you will need to use different punctuation in the formula. This is also assuming that the list you want to remove duplicates from is all in a single column. You may need to adjust the List:List range if you are pulling from multiple columns.
=IFERROR(MID("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row)) + 1, FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row + 1)) - (FIND("|", SUBSTITUTE("!" + JOIN(DISTINCT(COLLECT(List:List, List:List, @cell <> "")), "!") + "!", "!", "|", Number@row)) + 1)), "")
Formula is in [Column2] of the below snippet:
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.9K Get Help
- 377 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!