Arranging data

[Guest]

Hi all,

I have data like this

q c q c q c q c
a 1 10 2 20 3 30 4 40
b 5 50 6 60 7 70 8 80

I want to arrange the above data as follows:

a 1 2 3 4
10 20 30 40
b 5 6 7 8
50 60 70 80

That is a row has to be inserted, and alternative col data has to be
included and has to be deleted

I have lot of data like this in the format shown. I have to change it to the
new format
how can i do tihis?

 

[Dave Peterson]

How about something like this:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim iCol As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long

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

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

oRow = -1
For iRow = FirstRow To LastRow
oRow = oRow + 2
LastCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, 1).Value
oCol = 1
For iCol = FirstCol To LastCol Step 2
oCol = oCol + 1
NewWks.Cells(oRow, oCol).Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow + 1, oCol).Value _
= .Cells(iRow, iCol + 1).Value
Next iCol
Next iRow
End With

End Sub

This actually creates a new worksheet--so the original is still there.

 

[Guest]

Hi,

I get only the first row of data that is data relevant to "a". There are
many rows containing the data as arranged, and I want all the rows of data to
be arranged into the new format , how can it be done

Thanks.

 

[Dave Peterson]

I put this in Sheet1:

q c q c q c q c
1 1 10 2 20 3 30 4 40
2 5 50 6 60 7 70 8 80
3 9 90 10 100 11 110 12 120
4 13 130 14 140 15 150 16 160
5 17 170 18 180 19 190 20 200
6 21 210 22 220 23 230 24 240
7 25 250 26 260 27 270 28 280
8 29 290 30 300 31 310 32 320
9 33 330 34 340 35 350 36 360
10 37 370 38 380 39 390 40 400
11 41 410 42 420 43 430 44 440
12 45 450 46 460 47 470 48 480
13 49 490 50 500 51 510 52 520
14 53 530 54 540 55 550 56 560
15 57 570 58 580 59 590 60 600

After I ran that macro, I got this:
1 1 2 3 4
10 20 30 40
2 5 6 7 8
50 60 70 80
3 9 10 11 12
90 100 110 120
4 13 14 15 16
130 140 150 160
5 17 18 19 20
170 180 190 200
6 21 22 23 24
210 220 230 240
7 25 26 27 28
250 260 270 280
8 29 30 31 32
290 300 310 320
9 33 34 35 36
330 340 350 360
10 37 38 39 40
370 380 390 400
11 41 42 43 44
410 420 430 440
12 45 46 47 48
450 460 470 480
13 49 50 51 52
490 500 510 520
14 53 54 55 56
530 540 550 560
15 57 58 59 60
570 580 590 600


It sure looks like it works to me.