Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    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.

  2. 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!

    Excel 2013: The Missing Manual

    + 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!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    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 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()))
    Last edited by sdckapr; 2014-03-28 at 20:32.

  4. #3
    3 Star Lounger
    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.
    Quote Originally Posted by sdckapr View Post
    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.
    Quote Originally Posted by sdckapr View Post
    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.
    Quote Originally Posted by sdckapr View Post
    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.
    Quote Originally Posted by sdckapr View Post
    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.

  5. The Following User Says Thank You to boobounder For This Useful Post:

    THill (2014-04-01)

  6. #4
    WS Lounge VIP sdckapr's Avatar
    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.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •