PDA

View Full Version : Concatenate Multiple Rows



emcinco
11-29-2011, 04:36 AM
Can somebody please help? I’ve tried doing Nesting-IF formula and mix of functions to no avail. I think VBA will work out my problem but I do not know how to start a code for this:

I have to capture the all comments for the same ID. The ‘Comments New’ field in the attachment is my desired output. Not all the lines have the ID, if the ID field of the line is empty the comments should go with all other comments that goes before that line on the first instance of that ID.

Appreciate your help.

nilem
11-29-2011, 05:09 AM
Sub ertert()
Dim x, y(), i&, j&, s$
x = Range("A2:B" & Cells(Rows.Count, 2).End(xlUp).Row).Value
ReDim y(1 To UBound(x), 1 To 1): j = 1
For i = 1 To UBound(x)
If Len(x(i, 1)) Then
y(j, 1) = s: s = x(i, 2): j = i
Else
s = s & " " & x(i, 2)
End If
Next i
y(j, 1) = s: [c2].Resize(i - 1).Value = y()
End Sub

emcinco
11-29-2011, 05:54 AM
:rotlaugh:
It's working!!!
I just need to twick the code a little bit - - Dunno how. My comments are in column 'C' and my new comments must be added or placed in 'F'. I've tried but - - no success. Can you please help.

nilem
11-29-2011, 06:39 AM
try to replace these lines:
...
x = Range("A2:C" & Cells(Rows.Count, 2).End(xlUp).Row).Value
...
y(j, 1) = s: s = x(i, 3): j = i
...
s = s & " " & x(i, 3)
...
y(j, 1) = s: [F2].Resize(i - 1).Value = y() or show a new example

emcinco
11-29-2011, 08:20 PM
:rotlaugh:
It's working!!!
I just need to twick the code a little bit - - Dunno how. My comments are in column 'C' and my new comments must be added or placed in 'F'. I've tried but - - no success. Can you please help.


Sorry - My new comments should come in column E not in F.

emcinco
11-29-2011, 08:21 PM
Here is a sample sheet.

nilem
11-29-2011, 09:16 PM
.

emcinco
11-30-2011, 11:20 PM
Hi,
This really works wonderful. :rotlaugh:I've worked on simple VB's only. This one is complex for me.

Just another question. The VB does not seem to recognize 'operators'. It throws up error message.

I've attached a sample in the next post.

emcinco
11-30-2011, 11:22 PM
see attached.

nilem
12-01-2011, 12:09 AM
Try to add an apostrophe
If Len(x(i, 1)) Then
y(j, 1) = s: s = "'" & x(i, 2): j = i 'in this line
Else
s = s & " " & x(i, 2)
End If

emcinco
12-01-2011, 03:54 AM
Superb! :rotlaugh:
Thank you very much.