# Need help with formula

V

#### VanNorden

I have a spreadsheet like the below where Column A displays the result of an
IF function and then as a result Column B shows a name. In the table the are
300 rows so what I need is to automatically gather the names from Column B so
it they feed into a list on a separate sheet without all the blank cells
showing in between. Any suggestions?

A B
TRUE AARON
FALSE
FALSE
TRUE AARON

M

#### Mike H

Hi,

Put this ARRAY formula in (say) C1 and enter as an array and drag down. It
assumes your data are in B1:B50 but it doesn't matter if this is too large
but must be large enough to capture all the data.

=IF(ROWS(C\$1:C1)<=COUNT(B\$1:B\$50)+COUNTIF(B\$1:B\$50,"*?"),INDEX(B\$1:B\$50,SMALL(IF(B\$1:B\$50<>"",ROW(B\$1:B\$50)-ROW(B\$1)+1),ROWS(C\$1:C1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

V

#### VanNorden

Cheers Mike, that seems to be working perfectly!

Mike H said:
Hi,

Put this ARRAY formula in (say) C1 and enter as an array and drag down. It
assumes your data are in B1:B50 but it doesn't matter if this is too large
but must be large enough to capture all the data.

=IF(ROWS(C\$1:C1)<=COUNT(B\$1:B\$50)+COUNTIF(B\$1:B\$50,"*?"),INDEX(B\$1:B\$50,SMALL(IF(B\$1:B\$50<>"",ROW(B\$1:B\$50)-ROW(B\$1)+1),ROWS(C\$1:C1))),"")

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.