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

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" wrote:

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

Cheers Mike, that seems to be working perfectly!

"Mike H" wrote:

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" wrote:

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