formula to pull data from one column and combine duplicates

lauren davis

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

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

lauren davis

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

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.

lauren davis

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

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.

Jared Pietila

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?