Multiple rows from a single row
 

Hi,
I've got a worksheet with the following data (for example).
ID, Field 1, Field 2
1, hello, world
2, here, again

I'm looking to put it in the format

ID, FieldName, FieldValue
1, Field1, hello
1, Field2, world
2, Field1, here
2, Field2, again

As you can see I'm mapping the column headers into row values (column
2) and thier related value into row values (column3). For each
combination I've got to repeat the row ID.

I think this can only be done via a macro? Is this right..and can you
help (in reality I've got about 25 different fields and the number of
rows is variable)??

Thanks in advance
Nick

Multiple rows from a single row
 

One way is to use a macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 3).Value _
= Array("ID", "FieldName", "FieldValue")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
For iCol = 2 To .Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub

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

wrote:

Hi,
I've got a worksheet with the following data (for example).
ID, Field 1, Field 2
1, hello, world
2, here, again

I'm looking to put it in the format

ID, FieldName, FieldValue
1, Field1, hello
1, Field2, world
2, Field1, here
2, Field2, again

As you can see I'm mapping the column headers into row values (column
2) and thier related value into row values (column3). For each
combination I've got to repeat the row ID.

I think this can only be done via a macro? Is this right..and can you
help (in reality I've got about 25 different fields and the number of
rows is variable)??

Thanks in advance
Nick

--

Dave Peterson

Multiple rows from a single row
 

Hi, just for the fun of it, one way with formulas,

Assume you start your output in F6, with headers in F5:H5, input starts
at A2, with headers in A1:C1 (i.e. 3 columns).

In F6:
=OFFSET($A$1,1+INT(SUM(ROW()-ROW($F$6))/3),0)

In G6:
=OFFSET($A$1,0,1+MOD(SUM(ROW()-ROW($F$6)),3))

In H6:
=OFFSET($A$1,1+INT(SUM(ROW()-ROW($F$6))/3),1+MOD(SUM(ROW()-ROW($F$6)),3))

Copy down.

HTH
Kostis Vezerides


wrote:
Hi,
I've got a worksheet with the following data (for example).
ID, Field 1, Field 2
1, hello, world
2, here, again

I'm looking to put it in the format

ID, FieldName, FieldValue
1, Field1, hello
1, Field2, world
2, Field1, here
2, Field2, again

As you can see I'm mapping the column headers into row values (column
2) and thier related value into row values (column3). For each
combination I've got to repeat the row ID.

I think this can only be done via a macro? Is this right..and can you
help (in reality I've got about 25 different fields and the number of
rows is variable)??

Thanks in advance
Nick

Multiple rows from a single row
 

For another method check out this link:

http://j-walk.com/ss/excel/usertips/tip068.htm


wrote:

Hi,
I've got a worksheet with the following data (for example).
ID, Field 1, Field 2
1, hello, world
2, here, again

I'm looking to put it in the format

ID, FieldName, FieldValue
1, Field1, hello
1, Field2, world
2, Field1, here
2, Field2, again

As you can see I'm mapping the column headers into row values (column
2) and thier related value into row values (column3). For each
combination I've got to repeat the row ID.

I think this can only be done via a macro? Is this right..and can you
help (in reality I've got about 25 different fields and the number of
rows is variable)??

Thanks in advance
Nick