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

On Aug 28, 3:44*pm, Dearoledad
wrote:
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 - elegant in its simplicity. Thank you.

"Pete_UK" wrote:

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

On Aug 28, 3:44 pm, Dearoledad
wrote:
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're welcome - thanks for feeding back.

Pete

On Aug 28, 4:31*pm, Dearoledad
wrote:
Pete - elegant in its simplicity. Thank you.



"Pete_UK" wrote:
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

On Aug 28, 3:44 pm, Dearoledad
wrote:
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- Hide quoted text -

- Show quoted text -

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


"Dearoledad" wrote:

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

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

copy across to C1 and down as far as needed


"Dearoledad" wrote:

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

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.

"Teethless mama" wrote:

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

copy across to C1 and down as far as needed


"Dearoledad" wrote:

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