Moving data from column A to Columns B,C,D,E  
Author Message
copitta





PostPosted: Tue Mar 06 03:18:55 CST 2007 Top

Excel Programming >> Moving data from column A to Columns B,C,D,E

My data looks like this:

A. Rafanelli Winery
tel. +1 707.433.1385
4685 W. Dry Creek Road
Healdsburg, CA 95448

I want it to look like this:
A. Rafanelli Winery tel. +1 707.433.1385 4685 W. Dry Creek Road
Healdsburg, CA 95448
The data set is every four rows with a blank row inbetween is there a macro
out there for this other than copy>Pastespecial>transpose... A million times
over

Excel533  
 
 
martin_fishlock





PostPosted: Tue Mar 06 03:18:55 CST 2007 Top

Excel Programming >> Moving data from column A to Columns B,C,D,E Hi Michael:

This should do the job provided that the addresses are as specificed in
fixed blocks of 4 cells with a line between them.

Sub moveaddresses()

Dim lRow As Long

lRow = 1

With ActiveSheet
'get first row
Do While (.Cells(lRow, 1) = "")
lRow = lRow + 1
Loop
Do While (.Cells(lRow, 1) <> "")
' could do in for loop but only 3 cells
.Cells(lRow, 2) = .Cells(lRow + 1, 1)
.Cells(lRow, 3) = .Cells(lRow + 2, 1)
.Cells(lRow, 4) = .Cells(lRow + 3, 1)
.Rows(lRow + 1 & ":" & lRow + 4).Delete
lRow = lRow + 1
Loop
End With
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.




> My data looks like this:
>
> A. Rafanelli Winery
> tel. +1 707.433.1385
> 4685 W. Dry Creek Road
> Healdsburg, CA 95448
>
> I want it to look like this:
> A. Rafanelli Winery tel. +1 707.433.1385 4685 W. Dry Creek Road
> Healdsburg, CA 95448
> The data set is every four rows with a blank row inbetween is there a macro
> out there for this other than copy>Pastespecial>transpose... A million times
> over
 
 
MichaelP





PostPosted: Tue Mar 06 04:08:52 CST 2007 Top

Excel Programming >> Moving data from column A to Columns B,C,D,E Thank you so much you have no idea how much trial and error I have gone
through!!!!

If I am ever in Thailand I owe you big time!!!!

Thanks again,

Michael



> Hi Michael:
>
> This should do the job provided that the addresses are as specificed in
> fixed blocks of 4 cells with a line between them.
>
> Sub moveaddresses()
>
> Dim lRow As Long
>
> lRow = 1
>
> With ActiveSheet
> 'get first row
> Do While (.Cells(lRow, 1) = "")
> lRow = lRow + 1
> Loop
> Do While (.Cells(lRow, 1) <> "")
> ' could do in for loop but only 3 cells
> .Cells(lRow, 2) = .Cells(lRow + 1, 1)
> .Cells(lRow, 3) = .Cells(lRow + 2, 1)
> .Cells(lRow, 4) = .Cells(lRow + 3, 1)
> .Rows(lRow + 1 & ":" & lRow + 4).Delete
> lRow = lRow + 1
> Loop
> End With
> End Sub
>
> --
> Hope this helps
> Martin Fishlock, Bangkok, Thailand
> Please do not forget to rate this reply.
>
>

>
> > My data looks like this:
> >
> > A. Rafanelli Winery
> > tel. +1 707.433.1385
> > 4685 W. Dry Creek Road
> > Healdsburg, CA 95448
> >
> > I want it to look like this:
> > A. Rafanelli Winery tel. +1 707.433.1385 4685 W. Dry Creek Road
> > Healdsburg, CA 95448
> > The data set is every four rows with a blank row inbetween is there a macro
> > out there for this other than copy>Pastespecial>transpose... A million times
> > over
 
 
martin_fishlock





PostPosted: Tue Mar 06 04:21:14 CST 2007 Top

Excel Programming >> Moving data from column A to Columns B,C,D,E Your welcome.

There is anoter way to do it that I sometimes use if it is a one off.

1. Copy the column A and paste it in b,c,d
2. Delete cell b1, c1:c2 and d1:d3 moving the cells up.
3. Insert a column in column A and enter 1 in cells a1 and in a2
=if(a1=5,1,a1+1) copy that done the column and then copy the column and paste
special values it.
4. Copy that and paste it in the other cells in column a with data (should
give sequence 1,2,3,4,5,1,2,3,4,5
5. Select columns a:e
6. Sort on column 1
7. Delete all rows that are not = 1 in column A
8. delete column A
You should have it.

if you want it in the original order insert another column to the original
data and enter =row() and copy down and then copy paste special values it and
sort on the original row numbers at the end.

That is a quick way to do it.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.




> Thank you so much you have no idea how much trial and error I have gone
> through!!!!
>
> If I am ever in Thailand I owe you big time!!!!
>
> Thanks again,
>
> Michael
>

>
> > Hi Michael:
> >
> > This should do the job provided that the addresses are as specificed in
> > fixed blocks of 4 cells with a line between them.
> >
> > Sub moveaddresses()
> >
> > Dim lRow As Long
> >
> > lRow = 1
> >
> > With ActiveSheet
> > 'get first row
> > Do While (.Cells(lRow, 1) = "")
> > lRow = lRow + 1
> > Loop
> > Do While (.Cells(lRow, 1) <> "")
> > ' could do in for loop but only 3 cells
> > .Cells(lRow, 2) = .Cells(lRow + 1, 1)
> > .Cells(lRow, 3) = .Cells(lRow + 2, 1)
> > .Cells(lRow, 4) = .Cells(lRow + 3, 1)
> > .Rows(lRow + 1 & ":" & lRow + 4).Delete
> > lRow = lRow + 1
> > Loop
> > End With
> > End Sub
> >
> > --
> > Hope this helps
> > Martin Fishlock, Bangkok, Thailand
> > Please do not forget to rate this reply.
> >
> >

> >
> > > My data looks like this:
> > >
> > > A. Rafanelli Winery
> > > tel. +1 707.433.1385
> > > 4685 W. Dry Creek Road
> > > Healdsburg, CA 95448
> > >
> > > I want it to look like this:
> > > A. Rafanelli Winery tel. +1 707.433.1385 4685 W. Dry Creek Road
> > > Healdsburg, CA 95448
> > > The data set is every four rows with a blank row inbetween is there a macro
> > > out there for this other than copy>Pastespecial>transpose... A million times
> > > over
 
 
martin_fishlock





PostPosted: Tue Mar 06 04:21:31 CST 2007 Top

Excel Programming >> Moving data from column A to Columns B,C,D,E Your welcome.

There is anoter way to do it that I sometimes use if it is a one off.

1. Copy the column A and paste it in b,c,d
2. Delete cell b1, c1:c2 and d1:d3 moving the cells up.
3. Insert a column in column A and enter 1 in cells a1 and in a2
=if(a1=5,1,a1+1) copy that done the column and then copy the column and paste
special values it.
4. Copy that and paste it in the other cells in column a with data (should
give sequence 1,2,3,4,5,1,2,3,4,5
5. Select columns a:e
6. Sort on column 1
7. Delete all rows that are not = 1 in column A
8. delete column A
You should have it.

if you want it in the original order insert another column to the original
data and enter =row() and copy down and then copy paste special values it and
sort on the original row numbers at the end.

That is a quick way to do it.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.




> Thank you so much you have no idea how much trial and error I have gone
> through!!!!
>
> If I am ever in Thailand I owe you big time!!!!
>
> Thanks again,
>
> Michael
>

>
> > Hi Michael:
> >
> > This should do the job provided that the addresses are as specificed in
> > fixed blocks of 4 cells with a line between them.
> >
> > Sub moveaddresses()
> >
> > Dim lRow As Long
> >
> > lRow = 1
> >
> > With ActiveSheet
> > 'get first row
> > Do While (.Cells(lRow, 1) = "")
> > lRow = lRow + 1
> > Loop
> > Do While (.Cells(lRow, 1) <> "")
> > ' could do in for loop but only 3 cells
> > .Cells(lRow, 2) = .Cells(lRow + 1, 1)
> > .Cells(lRow, 3) = .Cells(lRow + 2, 1)
> > .Cells(lRow, 4) = .Cells(lRow + 3, 1)
> > .Rows(lRow + 1 & ":" & lRow + 4).Delete
> > lRow = lRow + 1
> > Loop
> > End With
> > End Sub
> >
> > --
> > Hope this helps
> > Martin Fishlock, Bangkok, Thailand
> > Please do not forget to rate this reply.
> >
> >

> >
> > > My data looks like this:
> > >
> > > A. Rafanelli Winery
> > > tel. +1 707.433.1385
> > > 4685 W. Dry Creek Road
> > > Healdsburg, CA 95448
> > >
> > > I want it to look like this:
> > > A. Rafanelli Winery tel. +1 707.433.1385 4685 W. Dry Creek Road
> > > Healdsburg, CA 95448
> > > The data set is every four rows with a blank row inbetween is there a macro
> > > out there for this other than copy>Pastespecial>transpose... A million times
> > > over