Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
transposing data
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
transposing data
i have a maximum of 48 rows
Thanks "Sriram" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
transposing data
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 -- Gary''s Student - gsnu200908 "Sriram" wrote: i have a maximum of 48 rows Thanks "Sriram" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
transposing data
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 On Oct 25, 6:20*pm, Sriram wrote: i have a maximum of 48 rows Thanks "Sriram" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing data? | Excel Worksheet Functions | |||
Transposing data | Excel Worksheet Functions | |||
transposing vertical data to horizontal with varying amount of data | Excel Discussion (Misc queries) | |||
Transposing data | Excel Discussion (Misc queries) | |||
Transposing data | New Users to Excel |