Hello, I have rows of data that needs to be concatenate together to form one
nice long string. Each row has a number assigned to it. Lets call it ticket
number.

Ticket number 1 has 4 rows of data that needs to be concatenate.
Ticket number 2 has 7 rows of data that needs to be concatenate.

How do I concatenate tickets that does not have a fixed number of row?
I tried using pivot table to count the number of rows each ticket has. Now
that I know, how can tell excel that this ticket has 4 rows to concatenate,
that ticket has 7 rows, etc?

A million thanks in advance.

The big question here is when you are looking at the data, how do you
identify how many rows for each ticket. Is there blank cell between them or
something?

It might require a macro but there must be a way of identifying where one
finishes and the next one starts even if it is in another column. Perhaps a
sample of your data for a couple of tickets will help.

Regards,

OssieMac

"rr94527" wrote:

Hello, I have rows of data that needs to be concatenate together to form one
nice long string. Each row has a number assigned to it. Lets call it ticket
number.

Ticket number 1 has 4 rows of data that needs to be concatenate.
Ticket number 2 has 7 rows of data that needs to be concatenate.

How do I concatenate tickets that does not have a fixed number of row?
I tried using pivot table to count the number of rows each ticket has. Now
that I know, how can tell excel that this ticket has 4 rows to concatenate,
that ticket has 7 rows, etc?

A million thanks in advance.

Hello OssieMac,

Here is a sample of data. If the line is 0, it is start of a new invoice.
Otherwise, it is same invoice as the previous line. What I am trying to do
is make it all in one line. See below for the sample output.

Invoice# Lines Comments
6634 0
6634 11
6634 12 comment 1 for invoice 6634
6634 13 con't comment for invoice 6634
5725 0
5725 11 comment 1 for invoice 5725
5725 12 con't comment for invoice 5725
5725 13 con't comment for invoice 5725
5725 14 con't comment for invoice 5725
5725 15 con't comment for invoice 5725
5725 16 con't comment for invoice 5725
5725 17 con't comment for invoice 5725
5725 18 con't comment for invoice 5725
5725 19 con't comment for invoice 5725
5725 20 con't comment for invoice 5725
2566 0
2566 11 comment 1 for invoice 2566
2566 12
2566 13 comment 2 for invoice 2566

Using invoice 6634, the output would be like this:
Invoice # Comments
6634 comment 1 for invoice 6634 con't comment for invoice 6634
2566 comment 1 for invoice 2566 comment 2 for invoice 2566

If you look at invoice #2566, there is a blank comment but belongs to same
invoice. I think the "Trim" function takes care of this. See above for the
desired output.

I sincerely hope you can figure out a solution to this problem. I have been
racking up my brains on this.

Thanks,
rr94527


"OssieMac" wrote:

The big question here is when you are looking at the data, how do you
identify how many rows for each ticket. Is there blank cell between them or
something?

It might require a macro but there must be a way of identifying where one
finishes and the next one starts even if it is in another column. Perhaps a
sample of your data for a couple of tickets will help.

Regards,

OssieMac

"rr94527" wrote:

Hello, I have rows of data that needs to be concatenate together to form one
nice long string. Each row has a number assigned to it. Lets call it ticket
number.

Ticket number 1 has 4 rows of data that needs to be concatenate.
Ticket number 2 has 7 rows of data that needs to be concatenate.

How do I concatenate tickets that does not have a fixed number of row?
I tried using pivot table to count the number of rows each ticket has. Now
that I know, how can tell excel that this ticket has 4 rows to concatenate,
that ticket has 7 rows, etc?

A million thanks in advance.

Hi again,

The following macro takes your data as the input data and places it in the
format you requested in another worksheet as the output data. You will need
to edit the sheet names between the double quotes just after the Dim
statements if your sheet names are not Sheet1 for the Input data and Sheet2
for the Output data.

It is always best to use a separate output so as not to destroy your
original data. However, ensure that you back up your workbook before
proceeding to install the macro and run it.

You have not indicated if you need help to install the macro so if you do
then please get back to me and I'll provide some instructions but let me know
what version of Excel you are using.

You will see the following comments 'Following line inserts a space between
comments and 'Alternative code inserts a linefeed to wrap text. You can only
use one of these and it is presently set for a space between comments. If you
want a linefeed to wrap the text then place a single quote at start of the
following line:

strComments = strComments & " "

and remove the single quote from the start of this line:

'strComments = strComments & Chr(10)




Sub Concat_Comments()

Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim rngInvoice As Range
Dim invNbr As Range
Dim strComments As String
Dim Invoice As Variant

'Edit with your input sheet name
Set wsInput = Sheets("Sheet1")

'Edit with your output sheet name
Set wsOutput = Sheets("Sheet2")

'Starts as row 2 because of column headers
'Ends at one cell past last data in 1st column.
With wsInput
Set rngInvoice = Range(.Cells(2, 1), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0))
End With

'Prepare output sheet with column headers
With wsOutput
.Cells(1, 1) = "Invoice#"
.Cells(1, 2) = "Comments"
End With

'Set Invoice equal to 1st cell in rngInvoice
Invoice = rngInvoice.Cells(1, 1)

For Each invNbr In rngInvoice
If invNbr < Invoice Then 'End of invoice number
With wsOutput
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = Invoice
.Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1) = strComments
End With
strComments = ""
Invoice = invNbr
End If

If Len(Trim(invNbr.Offset(0, 2))) 0 Then

If Len(Trim(strComments)) 0 Then
'Following line inserts a space between comments
strComments = strComments & " "

'Alternative code inserts a linefeed to wrap text
'strComments = strComments & Chr(10)
End If

strComments = strComments & Trim(invNbr.Offset(0, 2))

End If
Next invNbr

End Sub


Regards,

OssieMac

Hi OssieMac,

First, let me say THANKS for assisting me with this. I'm using Excel 2003
version. I know how to record a macro but how would I install your macro on
my laptop?

I absolutely agree that the output should be on another sheet so that the
original data does not get destroyed. Plus it will serve as a spot check to
make sure it is working as intented.

Thanks again,
rr94527

"OssieMac" wrote:

Hi again,

The following macro takes your data as the input data and places it in the
format you requested in another worksheet as the output data. You will need
to edit the sheet names between the double quotes just after the Dim
statements if your sheet names are not Sheet1 for the Input data and Sheet2
for the Output data.

It is always best to use a separate output so as not to destroy your
original data. However, ensure that you back up your workbook before
proceeding to install the macro and run it.

You have not indicated if you need help to install the macro so if you do
then please get back to me and I'll provide some instructions but let me know
what version of Excel you are using.

You will see the following comments 'Following line inserts a space between
comments and 'Alternative code inserts a linefeed to wrap text. You can only
use one of these and it is presently set for a space between comments. If you
want a linefeed to wrap the text then place a single quote at start of the
following line:

strComments = strComments & " "

and remove the single quote from the start of this line:

'strComments = strComments & Chr(10)




Sub Concat_Comments()

Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim rngInvoice As Range
Dim invNbr As Range
Dim strComments As String
Dim Invoice As Variant

'Edit with your input sheet name
Set wsInput = Sheets("Sheet1")

'Edit with your output sheet name
Set wsOutput = Sheets("Sheet2")

'Starts as row 2 because of column headers
'Ends at one cell past last data in 1st column.
With wsInput
Set rngInvoice = Range(.Cells(2, 1), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0))
End With

'Prepare output sheet with column headers
With wsOutput
.Cells(1, 1) = "Invoice#"
.Cells(1, 2) = "Comments"
End With

'Set Invoice equal to 1st cell in rngInvoice
Invoice = rngInvoice.Cells(1, 1)

For Each invNbr In rngInvoice
If invNbr < Invoice Then 'End of invoice number
With wsOutput
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = Invoice
.Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1) = strComments
End With
strComments = ""
Invoice = invNbr
End If

If Len(Trim(invNbr.Offset(0, 2))) 0 Then

If Len(Trim(strComments)) 0 Then
'Following line inserts a space between comments
strComments = strComments & " "

'Alternative code inserts a linefeed to wrap text
'strComments = strComments & Chr(10)
End If

strComments = strComments & Trim(invNbr.Offset(0, 2))

End If
Next invNbr

End Sub


Regards,

OssieMac

Hi again,

To install the macro:-

Open the workbook.
Alt/F11 to open the VBA editor.
Select Menu item Insert then select Module.
Copy the macro and paste it into the module.
Close the VBA editor (Cross in red background far top right)
Save workbook.

To run the macro from the worksheet:-

Select menu item Tools-Macro-Macros.
Select the macro name (Should default to it).
Click Run.

After run:-
You will need to autofit the columns in the output. If you selected the Wrap
Text option in the macro, then autofit the rows also. (Essential to do
columns first). You said that you know how to record a macro, so you could
record the columns and rows autofit then copy and paste it into the bottom of
the main macro for future use. (Note that when you record another macro in
the workbook, it will be on a new module and you will have to copy it from
there. You can then right click on the extra module in the Project Explorer
and remove it.)

If required, To insert a button to run the macro:-
There are two types of controls. Forms controls and ActiveX controls. The
Forms controls are on the Forms Toolbar and the ActiveX controls are on the
Control Toolbox toolbar.

The easy method for this project is the Forms control so if not already
displayed, then display the Forms toolbar. (Menu item View-Toolbars-Forms).
Select the button and the cursor turns to a fine line plus sign. Move the
cursor to the worksheet and then hold the left button down on the mouse and
drag the button out to the size required.
When you release the mouse button, the Assign Macro dialog box appears.
Select the macro name (Concat_Comments) then OK.
Highlight the default button name (Button1) and edit to the name you want.
Click anywhere on the worksheet.
Click on the button to run the macro.

Note that the macro that I have given you does not clear the output
worksheet before running. If there is anything on it then you should clear it
first.

Feel free to get back to me again if you have any other problems or the
macro doen not work exactly as you want.

I'll post the macro again so you have it all together with the instructions:-

Sub Concat_Comments()

Dim wsInput As Worksheet
Dim wsOutput As Worksheet
Dim rngInvoice As Range
Dim invNbr As Range
Dim strComments As String
Dim Invoice As Variant

'Edit with your input sheet name
Set wsInput = Sheets("Sheet1")

'Edit with your output sheet name
Set wsOutput = Sheets("Sheet2")

'Starts as row 2 because of column headers
'Ends at one cell past last data in 1st column.
With wsInput
Set rngInvoice = Range(.Cells(2, 1), _
.Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0))
End With

'Prepare output sheet with column headers
With wsOutput
.Cells(1, 1) = "Invoice#"
.Cells(1, 2) = "Comments"
End With

'Set Invoice equal to 1st cell in rngInvoice
Invoice = rngInvoice.Cells(1, 1)

For Each invNbr In rngInvoice
If invNbr < Invoice Then 'End of invoice number
With wsOutput
.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) = Invoice
.Cells(.Rows.Count, "A").End(xlUp).Offset(0, 1) = strComments
End With
strComments = ""
Invoice = invNbr
End If

If Len(Trim(invNbr.Offset(0, 2))) 0 Then

If Len(Trim(strComments)) 0 Then
'Following inserts a space between comments
strComments = strComments & " "

'Alternative inserts a linefeed to wrap text
'strComments = strComments & Chr(10)
End If

strComments = strComments & Trim(invNbr.Offset(0, 2))

End If
Next invNbr

End Sub




Regards,

OssieMac