Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Multiple Rows to one
Hello,
I'm trying to change the format of my data. Ex: apple 2 ab cd orange 5 ef gh banana 4 cd ef And I want to get it to the format: apple 2 ab cd orange 5 ef gh banana 4 cd ef Is there an easy way to do that? I can't figure it out with transpose or the offset function. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Multiple Rows to one
try this
Sub rearrangeem() Dim i As Long lr = Cells(Rows.Count, 1).End(xlUp).Row If lr / 2 < Int(lr / 2) Then lr = lr - 1 'MsgBox lr For i = lr To 2 Step -2 Cells(i - 1, 3) = Cells(i, 1) Cells(i - 1, 4) = Cells(i, 2) Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Demosthenes" wrote in message ... Hello, I'm trying to change the format of my data. Ex: apple 2 ab cd orange 5 ef gh banana 4 cd ef And I want to get it to the format: apple 2 ab cd orange 5 ef gh banana 4 cd ef Is there an easy way to do that? I can't figure it out with transpose or the offset function. Thanks! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Multiple Rows to one
Thanks, but I'd like to do it without using a macro. Do you know of another
way to do it? "Don Guillett" wrote: try this Sub rearrangeem() Dim i As Long lr = Cells(Rows.Count, 1).End(xlUp).Row If lr / 2 < Int(lr / 2) Then lr = lr - 1 'MsgBox lr For i = lr To 2 Step -2 Cells(i - 1, 3) = Cells(i, 1) Cells(i - 1, 4) = Cells(i, 2) Rows(i).Delete Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Demosthenes" wrote in message ... Hello, I'm trying to change the format of my data. Ex: apple 2 ab cd orange 5 ef gh banana 4 cd ef And I want to get it to the format: apple 2 ab cd orange 5 ef gh banana 4 cd ef Is there an easy way to do that? I can't figure it out with transpose or the offset function. Thanks! . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose Multiple Rows to one
With your test data in A1:B6, I used this:
In C1: =A2 In D1: =B2 Then I selected C1:D2 (4 cells!) And dragged it down as far as I needed. Then I selected columns C:D and converted to values. (edit|copy followed by edit|paste special values) Then I selected just column C Edit|Goto (or ctrl-g) special Blanks Edit|Delete|entirerow Demosthenes wrote: Hello, I'm trying to change the format of my data. Ex: apple 2 ab cd orange 5 ef gh banana 4 cd ef And I want to get it to the format: apple 2 ab cd orange 5 ef gh banana 4 cd ef Is there an easy way to do that? I can't figure it out with transpose or the offset function. Thanks! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transpose multiple Columns to rows | Excel Worksheet Functions | |||
how to transpose multiple rows into one column with row labeling? | Excel Discussion (Misc queries) | |||
transpose multiple rows into one column | Excel Discussion (Misc queries) | |||
transpose multiple rows at once | Excel Discussion (Misc queries) | |||
Can you transpose 1 column into multiple rows creating a table | Excel Discussion (Misc queries) |