Move alternating cells


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
 
Ad

Advertisements

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

Dearoledad

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
 
Ad

Advertisements

D

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top