Splitting Rows

Hello,

I have a rather large Excel sheet in which Row 1 contains a
Name and Row 2 the Address. This continues (Odd rows
having name, even address) throughout the entire sheet.
All the information is under column A. Is there a way to
split the information so that the odd rows stay in column A
and the even rows move to Column B?

Thank you in advance,
Jack

Jack

Put this formula in B1 and copy down.

=IF(MOD(ROW(A1),2)=1,OFFSET(A1,1,0),"")

Copy the formulae in column B and edit>paste special...>values back over
itself to 'kill' the formulae

You can then filter the column B by blanks (Data>Filter>autofilter) and
delete the filtered rows.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
[email protected]HIS

"jack" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a rather large Excel sheet in which Row 1 contains a
> Name and Row 2 the Address. This continues (Odd rows
> having name, even address) throughout the entire sheet.
> All the information is under column A. Is there a way to
> split the information so that the odd rows stay in column A
> and the even rows move to Column B?
>
> Thank you in advance,
> Jack

Assuming your data starts in A1 with a header, insert a couple of rows so it
now starts in A3.

Copy the whole of Col A and paste into Col B

in C3 put =MOD(ROW(),2) and copy down as far as your data goes

Select cell B1 and do edit / delete / shift cells UP

Select the data in Col C and do data Filter / Autofilter and then filter on
0

Select all the data in Col C and do edit / go to / special / visible cells
only, and then do edit / delete / entire row.

Job done.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"jack" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have a rather large Excel sheet in which Row 1 contains a
> Name and Row 2 the Address. This continues (Odd rows
> having name, even address) throughout the entire sheet.
> All the information is under column A. Is there a way to
> split the information so that the odd rows stay in column A
> and the even rows move to Column B?
>
> Thank you in advance,
> Jack

Assuming your data is on a sheet called "list", try this
in A1 of a new sheet, copy across to B1, and copy down:

=OFFSET(list!$A$1,ROW()*2+COLUMN()-3,)

HTH
Jason
Atlanta, GA

>-----Original Message-----
>Hello,
>
>I have a rather large Excel sheet in which Row 1
contains a
>Name and Row 2 the Address. This continues (Odd rows
>having name, even address) throughout the entire sheet.
>All the information is under column A. Is there a way to
>split the information so that the odd rows stay in
column A
>and the even rows move to Column B?
>
>Thank you in advance,
>Jack
>.
>