Move alternating cells

  • Thread starter Thread starter Dearoledad
  • Start date Start date
D

Dearoledad

I have a single column with about 3000 rows of street addresses and
city/states. I need move the city/state cells to the same row same the
related street.

What I have is this -
Column A
street 1
city 1
street 2
city 2
street 3
city 3

What I need is this -
Column A Column B
street 1 city 1
street 2 city 2
street 3 city 3
 
You can use a formula like this in B1:

=IF(MOD(ROW(),2)=1,A2,"")

and copy this down column B. Then fix the values by highlighting
column B, click <copy>, then Edit | Paste Special | Values (check) |
OK then <enter>.

Then apply autofilter to column B and select Blanks from the filter
pull-down (at the bottom of the list). Then highlight all the visible
rows and click on Edit | Delete Row, then select All from the filter
pull-down and remove the autofilter.

Hope this helps.

Pete
 
In B1 enter:
=INDIRECT("A" & 2*ROW()-1) and copy down
In C1 enter:
=INDIRECT("A" & 2*ROW()) and copy down
 
Pete - elegant in its simplicity. Thank you.

Pete_UK said:
You can use a formula like this in B1:

=IF(MOD(ROW(),2)=1,A2,"")

and copy this down column B. Then fix the values by highlighting
column B, click <copy>, then Edit | Paste Special | Values (check) |
OK then <enter>.

Then apply autofilter to column B and select Blanks from the filter
pull-down (at the bottom of the list). Then highlight all the visible
rows and click on Edit | Delete Row, then select All from the filter
pull-down and remove the autofilter.

Hope this helps.

Pete
Click to expand...
 
In B1: =OFFSET($A$1,COLUMNS($A:A)-1+(ROWS($1:1)-1)*2,)

copy across to C1 and down as far as needed
 
While Pete's idea was very helpful and worked great, yours is better because
it involves fewer steps to accomplish the goal. Thanks very much.
 
Back
Top