Consolidate unique values from multiple columns into single column

kioshi43 · 10/11/21

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?

Paul Newcome · 10/13/21

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...

https://community.smartsheet.com/discussion/comment/273631#Comment_273631

Krissia B. · 10/12/21

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

kioshi43 · 10/12/21

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.

Paul Newcome · 10/13/21

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...

https://community.smartsheet.com/discussion/comment/273631#Comment_273631

Adam Wasserman · 05/24/23

@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

Genevieve P. · 05/26/23

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:

https://community.smartsheet.com/discussion/comment/287334#Comment_287334

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

Paul Newcome · 05/26/23

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:

Consolidate unique values from multiple columns into single column

Best Answer

Answers

Help Article Resources

Categories