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


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



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

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


  • Genevieve P.
    Genevieve P. Employee Admin

    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?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!