Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can i wrap rows to form multiple rows per row to fit on 1 sheet? | Excel Discussion (Misc queries) | |||
Consolidate multiple spreadsheets into a single workbook | Excel Discussion (Misc queries) | |||
combining multiple rows of data into one single row of data | Excel Worksheet Functions | |||
generate multiple rows based on cell value | Excel Worksheet Functions | |||
how do I easily convert a single column of text (multiple rows si. | Excel Discussion (Misc queries) |