# Thread: Array Formula to Condense a List

1. 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.

2. 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. [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?

4. 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. a null string is still text

I did not know that - thank you!

#### Posting Permissions

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