Concatenate Data with Column Headings

[dserradas]

Hi Guys,

I am looking for code that concatenates data in sheet1 and places it into sheet2. The values need to be separated by a ^. Column A in sheet 1 should always be the prefix for the row. When the entire row has been concatenated, the script should jump down to the second row and continue.

My sample file shows how the data should be formatted.

I've searched through old posts for a solution but cannot find anything that matches. Any help would be appreciated!

[CodeNinja]

I think this is what you want.... in your test document, you will need to add a sheet as sheet3 is named sheet2 and sheet2 is actually missing...

[VBA]Sub test()
Dim iRow As Integer
Dim icol As Integer
Dim str As String
Dim iRow2 As Integer

iRow2 = 1
For iRow = 2 To Sheet1.Range("A65536").End(xlUp).Row
For icol = 2 To Sheet1.Range("IV1").End(xlToLeft).Column
Sheet2.Cells(iRow2, 1) = Sheet1.Cells(iRow, 1) & "^" & _
Sheet1.Cells(1, icol) & "^" & Sheet1.Cells(iRow, icol)
iRow2 = iRow2 + 1
Next icol
Next iRow


End Sub[/VBA]

[dserradas]

Wow, that was fast! I've added a sheet3 and the code works perfectly. Thanks so much for your help

[CodeNinja]

Glad to help. Please mark the thread as "solved" if you do not need any more help on this topic.