trying to store non-zero numbers from a list into sequential cells
 

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

trying to store non-zero numbers from a list into sequential cells
 

Put this in X1 and drag down to the same length as your column of numbers.

=IF(A1<0,A1,"")

I

"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

trying to store non-zero numbers from a list into sequential cells
 

Two other ideas:

1) Use Data/Filter/Autofilter on Column A (insert a row and add a header to
column A before turning on Autofilter). Click the drop down arrow and select
Custom, does not equal 0. Then copy/paste the results of the filter to x1.

2) Assuming your data is in A1:A8 (modify as needed), enter in x1:

=IF(ROWS(X$1:X1)COUNTIF(A$1:A$8,"<0")-COUNTBLANK(A$1:A$8),"",INDEX(A$1:A$8,SMALL(IF(A$1: A$8<0*ISNUMBER(A$1:A$8),ROW(INDIRECT("1:"&ROWS(A$ 1:A$8)))),ROWS(X$1:X1))))

But it must be entered with Cntrl+Shift+Enter (or you'll get #NUM when you
copy). Then copy it down column x as far as needed.



"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

trying to store non-zero numbers from a list into sequential cells
 

I would suggest DataFilterAutofilterCustom

greater than 0

Copt the results to somewhere that is not affected by the filter.

Non-zero data will be copied leaving no gaps.


Gord Dibben MS Excel MVP

On Sat, 3 Feb 2007 12:03:00 -0800, Ausseshooter
wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks

trying to store non-zero numbers from a list into sequential cells
 

Thanks fella's - much obliged

"Ausseshooter" wrote:

I am a complete novice with Excel, I am using Excel 2002 with windows xp
(home). I have list of numbers, some zero some none zero, I am trying to
extract the non-zero numbers and store them in sequential cells (i.e. if a10
then store it in x1,
if a20 then store it in x2, etc) but can't figure out how to get the cell to
iterate. There doesn't appear to be a "For" function ("for x=1 to 10, ...,
next") and you can only "nest" 7 "if" functions (which seems a terribly
cumbersome way of doing it anyhow). Any suggestions (please keep them simple
- I ain't all that bright) - thanks