PDA

View Full Version : Concatenate Data with Column Headings



dserradas
06-22-2012, 09:03 AM
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
06-22-2012, 11:10 AM
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...

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

dserradas
06-22-2012, 12:17 PM
Wow, that was fast! I've added a sheet3 and the code works perfectly. Thanks so much for your help :thumb

CodeNinja
06-22-2012, 12:29 PM
Glad to help. Please mark the thread as "solved" if you do not need any more help on this topic.