Results 1 to 5 of 5
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'm thinking about whether it is possible for an array formula to condense a list.
    If I have a column of numbers with blanks in the column can I use a formula to extract / condense those numbers into a list without blanks.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If I understand, this array (confirm with ctrl-shift-enter) should work:

    =INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100), ROW($A$1:$A$100)),ROW($A$1:$A$100)))

    Steve

    PS if you select a range longer than the number of elements, the last elements will be #NUM! error

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [quote name='sdckapr' post='783661' date='08-Jul-2009 15:50']If I understand, this array (confirm with ctrl-shift-enter) should work:

    =INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100), ROW($A$1:$A$100)),ROW($A$1:$A$100)))

    Steve

    PS if you select a range longer than the number of elements, the last elements will be #NUM! error[/quote]
    Post 783781 has a file attached that started me on this quest. I had no luck with your formula - although if I'm playing with plain numbers it works well.

    Other functions I tried NOT ISNUMBER, ISTEXT and played around with TYPE too.

    can you see the obvious thing I'm probably missing?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The Timepoints in the file are not numbers so ISNUMBER will not work. ISTEXT does not work since a null string is still text (only text with length of zero). You could use which makes sure the text is not a null.

    =INDEX($E$1:$E$26,SMALL(IF($E$3:$E$26<>"",ROW($E$3 :$E$26)),ROW($E$1:$E$26)))

    Note the index must start with row 1 since the formula looks at the rows. The 2nd row formula also must start at row1 since it is getting the the nth value from 1 to 26. The other 2 ranges start with the top of the data.

    Steve

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    a null string is still text

    I did not know that - thank you!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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