Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.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 copyPastespecialtranspose... A million times over |
#2
Posted to microsoft.public.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 "Martin Fishlock" wrote: 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. "Michael P." wrote: 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 copyPastespecialtranspose... A million times over |
#3
Posted to microsoft.public.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. "Michael P." wrote: 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 "Martin Fishlock" wrote: 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. "Michael P." wrote: 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 copyPastespecialtranspose... A million times over |
#4
Posted to microsoft.public.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. "Michael P." wrote: 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 "Martin Fishlock" wrote: 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. "Michael P." wrote: 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 copyPastespecialtranspose... A million times over |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
moving several columns into 1 continuous column | Excel Worksheet Functions | |||
Moving data from row to columns | New Users to Excel | |||
Moving data from column A to Columns B,C,D,E | Excel Programming | |||
Moving row data to columns in a one to many | Excel Programming | |||
Moving data from 3 columns to next 3 | Excel Programming |