Collapse range's entries by removing some values

Suppose A1.A9 looks like this:

1,2,=na(),4,=na(),6,7,8,9

I am looking for a way, ideally with (plain) formulas in B1.B7 to get:

1,2,4,6,7,8,9

If I were coding this, it would be easy to do with a do loop. But I'm looking for a solution that I can show people who may be pretty good at basic Excel, but who avoid VBA, array formulas, or programming outside of Excel.

At a more advanced level, ultimately, I'm going to want to do this where my missing values may be coded with not just =na(), but maybe a zero, or even an empty cell.

If you have blanks or even text, you can put in B1:
=SMALL($A$1:$A$9,ROW())

And copy it from B2:B7.

With the errors in it, I believe it requires an Array formula (confirm with ctrl-shift-enter):
=SMALL(IF(ISNUMBER($A$1:$A$9),$A$1:$A$9),ROW())

I have not thought of a way to use SUMPRODUCT to avoid the explicit array (sumproduct implicitly uses arrays, so can be confirmed as a normal formula, but has no if to eliminate the errors from the addition or multiplication).

Perhaps someone else will have better luck...

Steve
If your numbers will not be in ascending order than you could use something like the array:
=INDEX($A$1:$A$9,SMALL(IF(ISNUMBER($A$1:$A$9),ROW( $A$1:$A$9)),ROW()))

If you have blanks or even text, you can put in B1:
=SMALL($A$1:$A$9,ROW())

And copy it from B2:B7.

This works well. There are two problems (worked around here for people who have this problem in the future).

1) Using row() inside =small() can lead to problems if your data doesn't start in row 1. If, say, it starts in row 3, then you need to do this instead =SMALL($A$1:$A$9,ROW()-2)
2) Using small will generate an =na() if you feed it a range with empty cells. I worked around this by embedding my =small inside an =if(), like so: =IF(ROW()-2>COUNT(AJ$3:AJ$11),"",SMALL(AJ$3:AJ$11,ROW()-2))

2.

Originally Posted by sdckapr

With the errors in it, I believe it requires an Array formula (confirm with ctrl-shift-enter):
=SMALL(IF(ISNUMBER($A$1:$A$9),$A$1:$A$9),ROW())

This seems to be correct. Same problems as above can be solved the same way.

3.

Originally Posted by sdckapr

I have not thought of a way to use SUMPRODUCT to avoid the explicit array (sumproduct implicitly uses arrays, so can be confirmed as a normal formula, but has no if to eliminate the errors from the addition or multiplication).

Interesting. I can't see my way through that one yet ... (no problem, this seems like a lot of work, and I'm not really trying) ... but something like =sumproduct($A$1.A1,$C$1.C1) does deliver results from which you could filter out the missing values if column C is populated with ones or a mix of ones and blank cells.

4.

Originally Posted by sdckapr

If your numbers will not be in ascending order than you could use something like the array:
=INDEX($A$1:$A$9,SMALL(IF(ISNUMBER($A$1:$A$9),ROW( $A$1:$A$9)),ROW()))

I am not seeing the usefulness of this. I get the first method to work just fine with unordered numbers.