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