Split list via every other cell.

[Stu Pidaso]

Okay. I had someone show me what to do (on office 2003 I think) but I cannot
get it to work on 2007. I have a list I am trying to split with every other
number, odd and even. The list in column A is about 460 words. In column B,
I was shown to do this....

cell B1 =$A1
cell B2 =$A3

He then used the CTRL key to select both cells and then drag down. What this
did was a continuation of the odd numbers but let A appear before each
number. When I use the CTRL key in Office 2007 and select both cells, I do
not get the dark outline with the black dot at the bottom right that is
supposed to be used from grabbing and dragging. I have alos tried the shift
key which seems to select both but when dragging down does not do what this
person had shown me.

 

[Gord Dibben]

Stu

The method you think you were shown in 2003 would not work in 2003 so maybe you
saw something else.

You would get

=$A1
=$A3
=$A3
=$A5
=$A5
=$A7
=$A7

I would enter this formula in B1 and copy down to pick up every odd row value in
column A

=OFFSET($A$1,2*ROW()-2,0)

BTW............if you are not seeing the "fill handle" for grabbing, it may be
disabled in your "Edit" options.


Gord Dibben MS Excel MVP


On Sat, 22 Mar 2008 15:47:02 -0700, Stu Pidaso <Stu

 

[Stu Pidaso]

That's it!! Thanks! Now to really show my lack of skills. I also need the
formula for the even numbers. What would I change?

 

[T. Valko]

For the odd numbered rows enter this formula in B1:

=INDEX(A:A,ROWS(B$1:B1)*2-1)

For the even numbered rows enter this formula in C1:

=INDEX(A:A,ROWS(C$1:C1)*2)

Select both B1 and C1 and copy down as needed.

 

[Stu Pidaso]

That did the same thing. Either way both you guys are great. Thanks for the
help!!

 

[Gord Dibben]

=OFFSET($A$1,2*ROW()-1,0)


Gord

That's it!! Thanks! Now to really show my lack of skills. I also need the
formula for the even numbers. What would I change?

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Rick Rothstein \(MVP - VB\)]

Yet one more way....

For the odd numbered rows enter this formula in B1:

=OFFSET($A$1,2*ROW(A1)-2,0)

For the even numbered rows enter this formula in C1:

=OFFSET($A$1,2*ROW(A1)-1,0)

Select both B1 and C1 and copy down as needed.

Rick