Concatinate Rows of Text (1 Viewer)

[LadyDi]

I have a spreadsheet with all the service calls for a given customer and the call texts that the technicians entered for each call. The only problem is that the call texts are separated out by line. In other words, one call may have multiple lines of text, and thus the call number is repeated several times. Is there a formula that I can enter that will concatinate the call texts for every line for that call number?

For example:
This is what I currently have
Call # 1 Arrived at site
Call # 1 Turned equipment on
Call # 1 Removed jammed paper

This is what I want
Call # 1 Arrived at site Turned equipment on Removed jammed paper

Is that possible?

 

[godsspeed]

it wont be a straight forward formula....
does the above text sample show in the same column or multiple like below?
ColA | ColB |
Call#1 | arrived at site|
Call#1 | turned on equ...

 

[LadyDi]

They are in multiple columns.

 

[RayH]

Re: Concatenate Rows of Text

I created a macro for you to try.

Sub concat()
Dim n As Long
Dim lastcall As String
Dim calltext As String

x = 2 ' Destination column number
n = 2 ' Source Column number

lastcall = Cells(n, 1)  ' First Call Number
Do While Cells(n, 1) <> ""  ' Repeat until the call number is blank
calltext = ""   '  Reset concatenated call string
    Do While Cells(n, 1) = lastcall ' Has the call number changed? No=Continue else exit loop
        calltext = calltext + Cells(n, 2) + " " ' Join the text together
        n = n + 1 ' move down a row
    Loop
Cells(x, 9) = lastcall ' Put the call number in Column 9 (I) and row x
Cells(x, 10) = calltext ' Put the concatenated call text in Column 10 (J) and row x

lastcall = Cells(n, 1) ' Get next call number

x = x + 1 ' move down a row for destination
Loop
End Sub

For this to work the calls need to be sorted so they are together

 

[LadyDi]

That works great! Thank you very much for your help.