• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

concatenate from columns to cells

Pavel Zilber

New Member
I need to include in 1 cell data from number of cells as in this example below.
Thank you freinds.

Component part numberTop part number
Component part numberTops part numbers


Active Member
Here is a VBA solution for you.

Option Explicit

Sub ConCat()
    Dim i As Long, lr As Long, lr2 As Long
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    s1.Range("A2:B2").Copy s2.Range("A1")
    For i = 3 To lr
        If s1.Range("A" & i) = s1.Range("A" & i - 1) Then
            lr2 = s2.Range("B" & Rows.Count).End(xlUp).Row
            s2.Range("B" & lr2) = s2.Range("B" & lr2) & "/" & s1.Range("B" & i)
        Else: s1.Range("A" & i & ":B" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
End Sub
Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button