I need to create a formula that will look in column A (let's say rows
1:10000) for all non-zero numbers (both negative and positive numbers).
Several of the cells in column A could be zero or blank.

After the formula is populated with the non-zero numbers, I need for the
first number in the array formula to be placed in F1, the second number in
the array formula to be placed in F2, the third number in the array formula
to be placed in F3, etc.

Thanks.

Try this in F1, then copy down until you get #NUM

=INDEX($A$1:$A$9,SMALL(IF($A$1:$A49<0,ROW(INDIREC T("1:"&ROWS($A$1:$A$9)))),ROWS($F$1:$F1)))

or if you prefer blanks instead of #NUM
=IF(ROWS($F$1:$F1)SUM(--($A$1:$A$9<0)),"",INDEX($A$1:$A$9,SMALL(IF($A$1:$ A49<0,ROW(INDIRECT("1:"&ROWS($A$1:$A$9)))),ROWS($ F$1:$F1))))

both formulas array entered with Cntrl+Shift+Enter



"M Moore" wrote:

I need to create a formula that will look in column A (let's say rows
1:10000) for all non-zero numbers (both negative and positive numbers).
Several of the cells in column A could be zero or blank.

After the formula is populated with the non-zero numbers, I need for the
first number in the array formula to be placed in F1, the second number in
the array formula to be placed in F2, the third number in the array formula
to be placed in F3, etc.

Thanks.

Put Autofilter on the column and set it for non-zero using the custom
criteria. Then Copy and Paste (but not into the same rows - you may want to
go another sheet)

"M Moore" wrote:

I need to create a formula that will look in column A (let's say rows
1:10000) for all non-zero numbers (both negative and positive numbers).
Several of the cells in column A could be zero or blank.

After the formula is populated with the non-zero numbers, I need for the
first number in the array formula to be placed in F1, the second number in
the array formula to be placed in F2, the third number in the array formula
to be placed in F3, etc.

Thanks.