transposing data

[Sriram]

hi,

i have data in the below format in excel

A 1
B 2
C 3
D 4
(empty row)
(empty row)
A 5
B 6
C 7
D 8

and it runs into few hundred thousand rows, is there any way in which i
could transpose data in the format

A B C D
1 2 3 4
5 6 7 8
without having to paste special and then transpose record by record

Thanks

 

[Sriram]

i have a maximum of 48 rows

Thanks

 

[Gary''s Student]

Try this small macro:

Sub Xpose()
Dim r As Range, i As Long
Dim r2 As Range
Set r = Range("A1:A4")
r.Select
Set r2 = Range("B1")
For i = 1 To Rows.Count
If r(1).Value = "" Then Exit Sub
r.Copy
r2.PasteSpecial Transpose:=True
Set r = r.Offset(6, 0)
Set r2 = r2.Offset(1, 0)
Next
End Sub

It will put the transpose in column B, leaving the original data alone. For
example:

1 1 2 3 4
2 7 8 9 10
3 13 14 15 16
4 19 20 21 22
25 26 27 28
31 32 33 34
7 37 38 39 40
8
9
10


13
14
15
16


19
20
21
22


25
26
27
28


31
32
33
34


37
38
39
40


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[muddan madhu]

try this

assumed col A & Col b has data.

if you run the below macro, output will be from Col D.

Sub TEST_IT()
Dim r As Integer, z As Integer, r1 As Integer

z = 1
r1 = 1
r = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To r
If Cells(i, "b").Value = "" Then
z = 1
r1 = Cells(Rows.Count, "e").End(xlUp).Row + 1
ElseIf Cells(i, "B").Value <> "" Then
z = z + 1
Cells(r1, z + 2) = Cells(i, "B").Value
End If
Next i

End Sub