Splitting Rows

[jack]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Ken Wright]

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.

 

[Jason Morin]

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