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
dguillett@gmail.com
"Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
> 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
> dguillett@gmail.com
> "Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
> news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
> > 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 formulas>change to values>delete the no longer 
needed rows>Tedious.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message 
news:60F815BC-59E2-4F08-A971-80047EA20154@microsoft.com...
> 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
>> dguillett@gmail.com
>> "Demosthenes" <Demosthenes@discussions.microsoft.com> wrote in message
>> news:B37FAEE8-F62E-441F-8550-2723C5A91DFE@microsoft.com...
>> > 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 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