Collapse range’s entries by removing some values

Topic

New Reply

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.

Reply | Quote

Replies

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()))

Reply | Quote

There’s a lot to go with here, some of which works.

1.

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.

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.

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.

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.

Reply | Quote

Using row() inside =small() can lead to problems if your data doesn’t start in row 1.

That is true, but your indication was that it did…

2) Using small will generate an =na() if you feed it a range with empty cells.

This is an inaccurate statement. A #N/A error is generated if you have an =NA() in your data (other errors will generate other errors). Text and blanks are ignored. I got around this with ISNUMBER

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

That depends on your intent. The “Small” method will resort the list. It was not clear if your numbers would be in order. If you had a list:
12,11,=na(),4,=na(),6,7,8,9

The small method would give
4,6,7,8,9,11,12

The alternate INDEX method would keep them in the order presented and just collapse:
12,11,4,6,7,8,9

The INDEX method could also be adapted for lists with text and/or numbers if desired. The small method will not work with text.

Steve

Reply | Quote