Results 1 to 4 of 4

20140328, 12:51 #1
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 338
 Thanks
 3
 Thanked 1 Time in 1 Post
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.

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20140328, 20:29 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,196
 Thanks
 14
 Thanked 326 Times in 320 Posts
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 ctrlshiftenter):
=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()))Last edited by sdckapr; 20140328 at 20:32.

20140331, 13:31 #3
 Join Date
 Jun 2003
 Location
 Utah, USA
 Posts
 338
 Thanks
 3
 Thanked 1 Time in 1 Post
There's a lot to go with here, some of which works.
1.
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.
This seems to be correct. Same problems as above can be solved the same way.
3.
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.
I am not seeing the usefulness of this. I get the first method to work just fine with unordered numbers.

The Following User Says Thank You to boobounder For This Useful Post:
THill (20140401)

20140331, 14:12 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,196
 Thanks
 14
 Thanked 326 Times in 320 Posts
Using row() inside =small() can lead to problems if your data doesn't start in row 1.
2) Using small will generate an =na() if you feed it a range with empty cells.
I am not seeing the usefulness of this. I get the first method to work just fine with unordered numbers.
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