Home |
Search |
Today's Posts |
#1
|
|||
|
|||
moving alternate rows
I am currently working on a spreadsheet that has 11000+ rows. I need to move
every other row to a column. With this said, the rows must stay in order because A1 correspondes with A2, A3 with A4, A5 with A6, etc. So I have: name1 address1 name2 address2 name3 address3 I want: name 1 address1 name 2 address2 name 3 address 3 Are there any quick ways to do this? Thanks. |
#2
|
|||
|
|||
Here is a macro
Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row If (iLastRow \ 2) * 2 < iLastRow Then iLastRow = iLastRow - 1 End If For i = cLastRow To 2 Step -2 Cells(i - 1, "B").Value = Cells(i, "A").Value Rows(i).Delete Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "ad" wrote in message ... I am currently working on a spreadsheet that has 11000+ rows. I need to move every other row to a column. With this said, the rows must stay in order because A1 correspondes with A2, A3 with A4, A5 with A6, etc. So I have: name1 address1 name2 address2 name3 address3 I want: name 1 address1 name 2 address2 name 3 address 3 Are there any quick ways to do this? Thanks. |
#3
|
|||
|
|||
A method I have used ot do this is to enter in B1
=A2 I then copy B1 down to the bottom of the data. I then copy Column B and paste values over the B column In C1 I enter 1 in C2 I enter 2 and copy C1 and C2 down tro the bottom of the data making sure it stays 1 and 2 I then select all and select auto filter In column C I select 2 and delete all of the rows which have a 2 in them. or you can select all and sort by column C. Again delete all of the rows with a 2. If your data is on a page with other data you dont want to disturb, you can copy the data to a new sheet. and then copy it back when you are done. There are many more esoteric methods of doing something like this, but if I only have to do something once in a while, I prefer doing a brute force method. I have gotten into trouble by using esoteric methods for things they weren't designed for. "ad" wrote: If you have ot do this very often, I would set up a macro to do it. I am currently working on a spreadsheet that has 11000+ rows. I need to move every other row to a column. With this said, the rows must stay in order because A1 correspondes with A2, A3 with A4, A5 with A6, etc. So I have: name1 address1 name2 address2 name3 address3 I want: name 1 address1 name 2 address2 name 3 address 3 Are there any quick ways to do this? Thanks. |
#4
|
|||
|
|||
thanks bj, that worked perfect!
"bj" wrote: A method I have used ot do this is to enter in B1 =A2 I then copy B1 down to the bottom of the data. I then copy Column B and paste values over the B column In C1 I enter 1 in C2 I enter 2 and copy C1 and C2 down tro the bottom of the data making sure it stays 1 and 2 I then select all and select auto filter In column C I select 2 and delete all of the rows which have a 2 in them. or you can select all and sort by column C. Again delete all of the rows with a 2. If your data is on a page with other data you dont want to disturb, you can copy the data to a new sheet. and then copy it back when you are done. There are many more esoteric methods of doing something like this, but if I only have to do something once in a while, I prefer doing a brute force method. I have gotten into trouble by using esoteric methods for things they weren't designed for. "ad" wrote: If you have ot do this very often, I would set up a macro to do it. I am currently working on a spreadsheet that has 11000+ rows. I need to move every other row to a column. With this said, the rows must stay in order because A1 correspondes with A2, A3 with A4, A5 with A6, etc. So I have: name1 address1 name2 address2 name3 address3 I want: name 1 address1 name 2 address2 name 3 address 3 Are there any quick ways to do this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW CAN I AUTOMATICALLY INSERT ALTERNATE BLANK ROWS IN A LIST? | Excel Discussion (Misc queries) | |||
Select alternate rows to copy | Excel Discussion (Misc queries) | |||
How do I shade alternate rows in Excel 2002? | Excel Discussion (Misc queries) | |||
Use of Exact(or other) function for alternate rows? | Excel Worksheet Functions | |||
Identifying exact values in alternate rows | Excel Worksheet Functions |