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 editpaste special...values back over
itself to 'kill' the formulae

You can then filter the column B by blanks (DataFilterautofilter) and
delete the filtered rows.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"jack" wrote in 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

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" wrote in 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

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
.