Combine / concatenate data

[mtanker]

hi i want to combine A's every 7 cells to B column

Example;

a1 a2 a3 a4 a5 a6 a7 to b1
a8 a9 a10 a11 a12 a13 a14 to b2
.....
.....
.....
a664 a665 a666 a667 a668 a669 a670 to b88


like this

=CONCATENATE(A1; " ";A2;" ";A3;" ";A4;" ";A5
=CONCATENATE(A6; " ";A7;" ";A8;" ";A9;" ";A10
=CONCATENATE(A11; " ";A12;" ";A13;" ";A14;" ";A15
=CONCATENATE(A16; " ";A17;" ";A18;" ";A19;" ";A20

but i need macro code to be fast.. i have about 10000cells

thanks

 

[Joe4]

Welcome to the Board!

Try this:

Sub CombineData()

    Dim lastRow As Long
    Dim myARow As Long
    Dim myBRow As Long
    
'   Find last row in column A
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Set initial values
    myARow = 1
    myBRow = 1
    
    Application.ScreenUpdating = False
    
'   Loop through all data
    Do Until myARow > lastRow
'       Populate cell in column B
        Cells(myBRow, "B") = Cells(myARow, "A") & " " & Cells(myARow + 1, "A") & " " & Cells(myARow + 2, "A") _
                            & Cells(myARow + 3, "A") & " " & Cells(myARow + 4, "A") & " " _
                            & Cells(myARow + 5, "A") & " " & Cells(myARow + 6, "A")
'       Increment counters
        myARow = myARow + 7
        myBRow = myBRow + 1
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Process Complete!"
    
End Sub

 

[JoeMo]

This may be faster.

Sub mtanker()
Const howMany As Long = 7
Dim lR As Long, Vin As Variant, Vout As Variant, ct As Long, i As Long
lR = Cells(Rows.Count, "A").End(xlUp).Row
ReDim Vout(1 To lR, 1 To 1)
Do
    ct = ct + 1
    i = ct * howMany
    Vin = Range("A" & i - howMany + 1, "A" & i)
    Vout(ct, 1) = Join(Application.Transpose(Vin), " ")
    Erase Vin
Loop While ct <= lR - howMany
Application.ScreenUpdating = True
With Range("B1:B" & lR)
    .Value = Vout
    .EntireColumn.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[mtanker]

Thanks a lot!! it worked

 

[mtanker]

Thank you very much

 

[Rick Rothstein]

Here is another macro (I believe even faster yet) that should work for you...

[table="width: 500"]
[tr]
	[td]Sub ConcatEverySeven()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("B1:B" & Application.RoundUp(LastRow / 7, 0)) = Application.Transpose(Split(Join(Application.Transpose(Evaluate(Replace("IF(MOD(ROW(A1:A#),7),A1:A#,A1:A#&""|"")", "#", LastRow)))), "|"))
End Sub[/td]
[/tr]
[/table]

Note: This macro has a limitation... it can only handle up to a maximum of 65535 rows.

 

[dfolzenlogen]

Hi Joe4,

Love that you comment your code. It sure helps newbies like me understand what's going on!

Thanks for your posts.

D

 

[Joe4]

Love that you comment your code. It sure helps newbies like me understand what's going on!

Thanks for noticing!

Yes, that is one of my goals, is to try to teach so people can learn how to do it themselves, and to know what is going on so they can support/change the code, if need be.