Help with writing a macro to concatenate data

[acrasherkid]

Hi Guys,

First of all I have been a huge fan of this forum for years veyr good work thanks a bunch!

My problem is I have data in the same format each month and need to concatenate each row with a comma between them. The data I need to concatenate is in column E and the the number of rows are denoted in column F, this never changes. In column F the first row may state 1 the second row 2 etc until the text is finished for example in this case 7. Then it will start again 1 to however many there are. I need to create a macro that looks in column f to see how many rows of data should be concatenated in column e.

Example

Column E Column F
Mr 1
Excel 2
Hi 1
My 2
Name 3
Is 4

I think you see the point! Rather than manually concatenate each row I need the macro to put the concatenated text in Column G.

Please help

 

[acrasherkid]

Column E Column F Column G
Mr 1 Mr Excel
Excel 2
Hi 1 Hi My Name Is
My 2
Name 3
Is 4

 

[Firefly2012]

Please could you give an example of output given those inputs - if you use Internet Explorer, you could surround the relevant range with borders and then copy and paste from Excel into your reply. It will make it obvious to anyone reading what it is you are attempting to do

 

[acrasherkid]

Please could you give an example of output given those inputs - if you use Internet Explorer, you could surround the relevant range with borders and then copy and paste from Excel into your reply. It will make it obvious to anyone reading what it is you are attempting to do

Ok hopefulyl this will work!

Column E</SPAN>	Column F</SPAN>	Column G (Concatenated text from macro)</SPAN>
Mr</SPAN>	1</SPAN>	Mr Excel</SPAN>
Excel</SPAN>	2</SPAN>
Hi</SPAN>	1</SPAN>	Hi My Name Is</SPAN>
My </SPAN>	2</SPAN>
Name</SPAN>	3</SPAN>
Is</SPAN>	4</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2><COL></COLGROUP>

 

[acrasherkid]

Is the above clear?
I have 7000 rows of data so once the macro can no longer find a number (column f) I would like it to stop. As you can imagine the range can be 1 or 1-2 or 1-3 etc etc

Column F relates to how many rows of text relate to that item in case it is not obvious. Mr Excel is made up of 2 lines. Ideally I would like the concatenated text in Column G where column F is equal to 1

 

[Firefly2012]

I didn't see any commas being concatenated, but see if this does what you want:

Sub Conc_Data()
Dim vIn, vOut
Dim i As Long, j As Long, cnt As Long

vIn = Range("E1:F" & Cells(Rows.Count, "F").End(xlUp).Row).Value

ReDim vOut(1 To Application.CountIf(Range("F:F"), 1), 1 To 1)

j = 0
For i = 1 To UBound(vIn, 1)
    If vIn(i, 2) = 1 Then
        j = j + 1
        vOut(j, 1) = vIn(i, 1)
    Else
        vOut(j, 1) = vOut(j, 1) & " " & vIn(i, 1)
    End If
Next i

Range("G1").Resize(UBound(vOut, 1)).Value = vOut
    
End Sub

 

[acrasherkid]

Thanky ou so very muchf or your reply Firefly. I am trying to insert in the workbook then run but it keeps saying subscript out of range. Is there any chance you could let me know the steps required to make this work in my macro enabled workbook? sorry to be very very thick

Is there any chance I could email the workbook? Sorry to be apain I REALLY appreciate all of your help

 

[Firefly2012]

What line is highlighted when the code fails?

 

[acrasherkid]

I insert into module, save as macro enabled workbook. Go to macros select it then run and it states subscript out of range, when I go into visual basic and try to run and get an error.

Sorry I removed my headers and now when I select run I get run time error 1004.application defined or object defined error. I am really sorry I have not used VBA for a very long time!!!!

 

[acrasherkid]

I have been messing about in a blank workbook and it works let me play some more! I think ti was my headers buddy