Consolidate Multiple Column Cells Into 1 Based on Cell Value in Row

JaHunter

I am working on a telecom cleanup project and have an export of all the telephone numbers at each site. I'm wanting to take the telephone numbers in each cell and combine them into 1 based on the branch number. The closest thing I could figure out was to run a collect function based on the branch number but am getting an incorrect argument error. Anyone know a formula that would work for this?

=INDEX(COLLECT(WTN:WTN, [Branch Number]:[Branch Number] = "FL24"))

Paul Newcome

Try replacing the INDEX function with a JOIN function.

JaHunter

Hey, Paul. I can run a join but it would have to be done manually for each site. There are 2000 locations, so I was hoping we could get a formula and apply it to the column to do it for each location based on an IF function or something like that. Would it be better to try and run this off a sheet reference instead?

Changed INDEX to Join with the same formula but came back as invalid operation.

=JOIN(WTN:WTN, [Branch Number]:[Branch Number] = "FL24"))

Tried adding an IF function but got the same incorrect argument issue.

=JOIN([Service ID]:[Service ID], IF([Address 1]:[Address 1] = "FL24"))

Paul Newcome

Can you manually enter the expected output into a few rows so we can see what exactly you are wanting to accomplish?

JaHunter

Sure thing! See below. Basically we want all the telephone numbers to be combined into one cell so we can have 1 row per site instead of multiple. Currently, there are so many rows it is hard to manage in either Excel or Smartsheet. The example of what we would rather have is entered into the Notes column.

Paul Newcome

What happened to the COLLECT function? You still need that. You just needed to change the INDEX to a JOIN.

JaHunter

=JOIN(COLLECT(WTN:WTN, [Branch Number]:[Branch Number] = "FL24")) <- Still coming back as incorrect argument.

Paul Newcome

There should be a comma between the range and criteria.

JaHunter

That worked! New problem...I need to add a delimiter. Now I have a telephone number that could rival pi...

Tried =JOIN(COLLECT(WTN:WTN, [Branch Number]:[Branch Number], ="FL24" + ", ")) and =JOIN(COLLECT(WTN:WTN, [Branch Number]:[Branch Number], ="FL24", ", ")) but neither worked.

JaHunter

Wait! I figured that part out! We are all set. Thank you so much for your help, Paul. You have saved my sanity.

Paul Newcome

Happy to help. 👍️

Blakeozzy

Hi Paul,

We are currently using the formula below and it works great for the Branch Number FL24. Is there a way that we could change the formula to be formatted to work on all columns by simply dragging it through the rest of the notes?

=JOIN(COLLECT(WTN:WTN, [Branch Number]:[Branch Number], ="FL24"), ", ")

Paul Newcome

You would replace the "specific text" with a cell reference [Column Name]@row.