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

Answers

  • Krissia B.
    Krissia B. Moderator

    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
    kioshi43 ✭✭✭

    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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!