| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Split list via every other cell.
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
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? "Gord Dibben" wrote: 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 wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
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. -- Biff Microsoft Excel MVP "Stu Pidaso" <Stu wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
That did the same thing. Either way both you guys are great. Thanks for the
help!! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Stu Pidaso" <Stu wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
=OFFSET($A$1,2*ROW()-1,0)
Gord On Sat, 22 Mar 2008 16:36:01 -0700, Stu Pidaso wrote: 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? "Gord Dibben" wrote: 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 wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Stu Pidaso" wrote in message ... That did the same thing. Either way both you guys are great. Thanks for the help!! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "Stu Pidaso" <Stu wrote in message ... 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Split list via every other cell.
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 "Stu Pidaso" <Stu wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| List Sorted/Split into groups | Excel Worksheet Functions | |||
| split email id list into 1 extra list with everything after "@" | Excel Worksheet Functions | |||
| split a cell diagonally in excell - a calendar -2 dates in 1 cell | Excel Discussion (Misc queries) | |||
| How can I split one cell into two cell parts (upper & lower)? | Excel Discussion (Misc queries) | |||
| How do I split a list by every other one in excel? | Excel Discussion (Misc queries) | |||
Linear Mode
