Putting column values in multiple rows
Hi, I am using SPSS data and would like to rearrange it in Excel.
The original file contains rows with 64 columns, one row representing 1 record: Example: Code mis1 ebus1 cont1 mis2 ebus2 cont2 626 5 6 1 .... (9 values) ... 2 8 7 .... Now I would like to have, by 9 column values one row, resulting in 7 rows by record: Example: Code mis ebus cont 626 5 6 1 ... 2 8 7 ... 1 1 4 ... 7 7 1 ... 6 6 1 ... 7 7 5 ... 7 7 1 ... Any help would be appreciated ! |
Putting column values in multiple rows
If I were doing this, I'd put that code on each line. It would make filtering
much easier! This creates a new worksheet and transfers the values over: Option Explicit Sub testme() Dim curWks As Worksheet Dim newWks As Worksheet Dim iRow As Long Dim iCol As Long Dim oRow As Long Set curWks = Worksheets("sheet1") Set newWks = Worksheets.Add newWks.Range("a1").Resize(1, 4).Value _ = Array("Code", "mis", "ebus", "cont") oRow = -5 With curWks For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row oRow = oRow + 7 For iCol = 2 To 64 Step 9 newWks.Cells(oRow, "A").Resize(7, 1).Value _ = .Cells(iRow, "A").Value newWks.Cells(oRow + (iCol - 2) / 9, "B").Resize(1, 9).Value _ = .Cells(iRow, "A").Offset(0, (iCol - 2 / 9) - 1) _ .Resize(1, 9).Value Next iCol Next iRow End With End Sub If you really only want that code on the first row of the group, change this: newWks.Cells(oRow, "A").Resize(7, 1).Value _ = .Cells(iRow, "A").Value to newWks.Cells(oRow, "A").Value _ = .Cells(iRow, "A").Value ChBoodts wrote: Hi, I am using SPSS data and would like to rearrange it in Excel. The original file contains rows with 64 columns, one row representing 1 record: Example: Code mis1 ebus1 cont1 mis2 ebus2 cont2 626 5 6 1 .... (9 values) ... 2 8 7 .... Now I would like to have, by 9 column values one row, resulting in 7 rows by record: Example: Code mis ebus cont 626 5 6 1 ... 2 8 7 ... 1 1 4 ... 7 7 1 ... 6 6 1 ... 7 7 5 ... 7 7 1 ... Any help would be appreciated ! -- Dave Peterson |
All times are GMT +1. The time now is 01:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com