# Move alternating cells

D

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

P

#### Pete_UK

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

G

#### Gary''s Student

In B1 enter:
=INDIRECT("A" & 2*ROW()-1) and copy down
In C1 enter:
=INDIRECT("A" & 2*ROW()) and copy down

D

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

T

#### Teethless mama

In B1: =OFFSET(\$A\$1,COLUMNS(\$A:A)-1+(ROWS(\$1:1)-1)*2,)

copy across to C1 and down as far as needed

P

#### Pete_UK

You're welcome - thanks for feeding back.

Pete

Pete - elegant in its simplicity. Thank you.

- Show quoted text -

D