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 !

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