formula to pull data from one column and combine duplicates

This is probably something pretty easy and in excel I would use the consolidate function. I just need a formula to pull data from one column to another, but I need duplicate names left out


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to show a mock-up of what you are trying to accomplish?

  • here is a screen shot example. So the highlighted cell I am trying to pull from the client column without having to type the name in manually. I want to be able to use a formula that will pull from the client column into column 4, but don't want it to pull duplicate names.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are going to need to use something along the lines of...

    =JOIN(DISTINCT(Client:Client), ",")

    to pull each unique entry into a comma delimited string.

    Put this in the first row of a helper column named String. Then create a second helper column named Comma.

    You should then be able to adapt the formulas on THIS SHEET to fit your needs for parsing the list of unique values.

  • thank you for your response. Once I have it in a helper column named String (compiles every client name into one column separated by a comma) I just need to use one of the formulas provided to list out each client on a separate row/same column?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would set up a "String" column and a "Comma" column and enter the formulas on the left side of the sheet into the corresponding cells taking the "Notes" into account. The String and Comma columns can be hidden after setting up so that your sheet stays looking clean.

  • I got the string and comma columns working, but when I get to the last of the list, it repeats the list from the top. Is there an Iferror I can add to stop this?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!