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!

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!

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!

.

I guess you could do with formulaschange to valuesdelete the no longer
needed rowsTedious.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Demosthenes" wrote in message
...
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!

.

With your test data in A1:B6, I used this:

In C1: =A2
In D1: =B2

Then I selected C12 (4 cells!)
And dragged it down as far as I needed.

Then I selected columns C 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