Move alternating cells

[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

 

[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

 

[Gary''s Student]

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

 

[Dearoledad]

Pete - elegant in its simplicity. Thank you.

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

 

[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

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

Pete - elegant in its simplicity. Thank you.







- Show quoted text -

 

[Dearoledad]

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.