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

[Ausseshooter]

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 a1>0
then store it in x1,
if a2>0 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

 

[Mike]

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

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

I

 

[JMB]

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.

 

[Gord Dibben]

I would suggest Data>Filter>Autofilter>Custom

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

 

[Ausseshooter]

Thanks fella's - much obliged