Transforming sheet1 rows into a single sheet2 col

[Strijder]

I have a large number of rows in sheet one with columns A-Q (A1, B1, C1
etc); the first line is a header. In a second sheet, I'm trying t
combine the header and each row and then move them into a singl
column, with a space between the transposed records of each row, ie:

A1&A2
B1&B2
...
Q1&Q2

A1&A3
B1&B3
...
Q1&Q3

A1&A4
B1&B4
...
Q1&Q4

etc.

Unfortunately, my column references keep jumping rows in sheet1 when
fill down:

A1&A2
B1&B2
...
Q1&Q2

A1&A20
B1&B20
...
Q1&Q20

Does anyone have any suggestions on how I can consolidate th
rows&columns from Sheet1 into a single column with a line betwee
records?

Thanks in advance =

 

[Dave Peterson]

How about a macro instead of a worksheet formula?

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iCol As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim HeaderRow As Long
Dim oRow As Long

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
HeaderRow = 1
FirstRow = HeaderRow + 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
For iRow = FirstRow To LastRow
For iCol = .Range("a1").Column To .Range("q1").Column
oRow = oRow + 1
newWks.Cells(oRow, "A").Value _
= .Cells(HeaderRow, iCol).Value _
& "--" & .Cells(iRow, iCol).Value
Next iCol
oRow = oRow + 1
Next iRow
End With

Application.ScreenUpdating = True

End Sub


(I separated the values with "--". You didn't ask for this, though. If you
don't want any separation, just change "--" to "".)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm