Need help with formula

[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

 

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

 

[VanNorden]

Cheers Mike, that seems to be working perfectly!

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.