Shifting data

  • Thread starter Thread starter Tony Corneto
  • Start date Start date
T

Tony Corneto

I have a very long column of data around 5000 cells
long. Is there a way to shift just the even or just the
odd cells to the next column? The evens are names.. the
odds are offices. I need to be able to categorize them.
There must be a way I just can't figure it out.

Thanks in advance
 
Tony,

try-

=IF(MOD(ROW(),2)=0,$A2,"")

OR----

=IF(MOD(ROW(),2)=1,$A2,"")

regards,

JohnI
 
Tony

Assuming your data is in Column A........

In B1 enter this formula..........

=INDEX($A$1:$A$5000,(ROW()-1)*2+COLUMN()-1,1)

The 5000 is an arbitrary number. Your range may differ.

Drag/copy across to C1 then down Columns B:C as far as you need until zeros
start to show up.

When happy with the results in columns B and C you can select these columns
and Copy them. Then Paste Special(in place)>Values.

Delete Column A.

Gord Dibben Excel MVP XL2002
 
Tony,

=IF(MOD(ROW(),2)=0,$A2,"")
Put the first formula (above) in the first blank column (on Row 2 for
example) to the left of your data.
Change the $A2 to be your first line of data. Drag the formula down.

=IF(MOD(ROW(),2)=1,$A2,"")
Put the other formula (above) in the next blank column & change also. Drag
down.

All of the even rows will appear in one column, & all of the odd rows in the
other.

If you want both lines of the data to appear on the same row, change the
second formula to be-
=IF(MOD(ROW(),2)=0,$A3,"")

MOD works by dividing the cell ROW number by two (2) and giving a remainder,
either 0 or 1.
The formula uses this remainder to determine if the cell is in an even row
(remainder 0) or odd row (remainder 1).

regards,

JohnI
 
Your replies with dragging a column refer to using the Fill Handle,
you want to grab the fill handle which is near the bottom of the
cell and is a little bulge on the left or right sided. Actually since
you have data to the left you can select your two additional cells
and double-click the fill handle.

More information on the fill handle on
http://www.mvps.org/dmcritchie/excel/fillhand.htm

The use of the Copy, Paste Special, Values
is the remove the formula dependencies so that you can
delete the original column.

After you delete half of the rows on your sheet your last used cell
will still indicate that you have 5000 rows. Delete the unwanted
rows (that only have data in Column A) and then Save your with
File,Save.
 
Back
Top