Need No Blanks List based on two conditions

[Chaturanga]

Hello,


I'm looking for a worksheet formula that will produce a list of no
blank values in a third column. First column contains the text values
(30 rows) and they already listed with no blanks. The second column has
boolean TRUE or FALSE values corresponding with the values in the first
column. I'm looking for a formula that will list all first column
values with corresponding second column values that are FALSE in the
third column with no blanks.

Thanks

[Bob Phillips]

Select a block of cells in another column, best to be same size as the data,
and enter this formula in the formula bar

=IF(ISERROR(SMALL(IF(NOT($B$1:$B$20),ROW($A1:$A20),""),ROW($A1:$A20))),"",IN
DEX($A$1:$A$20,SMALL(IF(NOT($B1:$B20),ROW($A1:$A20),""),ROW($A1:$A20))))

as an array formula, so commit with Ctrl-Shift-Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chaturanga" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
>
> I'm looking for a worksheet formula that will produce a list of no
> blank values in a third column. First column contains the text values
> (30 rows) and they already listed with no blanks. The second column has
> boolean TRUE or FALSE values corresponding with the values in the first
> column. I'm looking for a formula that will list all first column
> values with corresponding second column values that are FALSE in the
> third column with no blanks.
>
> Thanks
>

[Chaturanga]

Bob, I couldn't get this to work. There was no error with the formula
but there is no data revealed with it either. All blanks. Any
suggestions?

[Chaturanga]

Sorry Bob,

I created some data on a new sheet and your array worked just fine. The
data from the first attempt is created from an array formula so that
must create complications. I'll play with this for a while. Thanks
again.

[Aladin Akyurek]

Let A2:A25 be your first column range with A2 housing a label.

In B1 enter: 0

In B2 enter: Idx [ which is just a label ]

In B3 enter & copy down:

=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B1:B25)

In C2 enter: No Blanks List

In C3 enter & copy down:

=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")

From C3 on, you'll have the desired list with no interspersed
blank/empty cells.

Chaturanga wrote:
> Hello,
>
>
> I'm looking for a worksheet formula that will produce a list of no
> blank values in a third column. First column contains the text values
> (30 rows) and they already listed with no blanks. The second column has
> boolean TRUE or FALSE values corresponding with the values in the first
> column. I'm looking for a formula that will list all first column
> values with corresponding second column values that are FALSE in the
> third column with no blanks.
>
> Thanks
>

[Stefano Condotta]

Hello Aladin,

I'm afraid your final formula produces a curcular reference error. $C$3
cannot be in the formula. Column B works well. I'm trying to get it to work.
Thanks.
"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Let A2:A25 be your first column range with A2 housing a label.
>
> In B1 enter: 0
>
> In B2 enter: Idx [ which is just a label ]
>
> In B3 enter & copy down:
>
> =IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")
>
> In C1 enter:
>
> =LOOKUP(9.99999999999999E+307,B1:B25)
>
> In C2 enter: No Blanks List
>
> In C3 enter & copy down:
>
> =IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>
> From C3 on, you'll have the desired list with no interspersed blank/empty
> cells.
>
> Chaturanga wrote:
>> Hello,
>>
>>
>> I'm looking for a worksheet formula that will produce a list of no
>> blank values in a third column. First column contains the text values
>> (30 rows) and they already listed with no blanks. The second column has
>> boolean TRUE or FALSE values corresponding with the values in the first
>> column. I'm looking for a formula that will list all first column
>> values with corresponding second column values that are FALSE in the
>> third column with no blanks.
>>
>> Thanks
>>

[Aladin Akyurek]

Stefano,

That should not happen. Would you post the formulas as you adjusted them
to your data?

Stefano Condotta wrote:
> Hello Aladin,
>
> I'm afraid your final formula produces a curcular reference error. $C$3
> cannot be in the formula. Column B works well. I'm trying to get it to work.
> Thanks.
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>Let A2:A25 be your first column range with A2 housing a label.
>>
>>In B1 enter: 0
>>
>>In B2 enter: Idx [ which is just a label ]
>>
>>In B3 enter & copy down:
>>
>>=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")
>>
>>In C1 enter:
>>
>>=LOOKUP(9.99999999999999E+307,B1:B25)
>>
>>In C2 enter: No Blanks List
>>
>>In C3 enter & copy down:
>>
>>=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>>
>>From C3 on, you'll have the desired list with no interspersed blank/empty
>>cells.
>>
>>Chaturanga wrote:
>>
>>>Hello,
>>>
>>>
>>>I'm looking for a worksheet formula that will produce a list of no
>>>blank values in a third column. First column contains the text values
>>>(30 rows) and they already listed with no blanks. The second column has
>>>boolean TRUE or FALSE values corresponding with the values in the first
>>>column. I'm looking for a formula that will list all first column
>>>values with corresponding second column values that are FALSE in the
>>>third column with no blanks.
>>>
>>>Thanks
>>>
>
>
>

[Stefano Condotta]

The formula:

=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")

poduces a circular reference if entered in cell C3 (as I read your
instructions). I did not adapt to my situation as I tested it out on a clean
worksheet first.

Regards,
Stefano

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
> Stefano,
>
> That should not happen. Would you post the formulas as you adjusted them
> to your data?
>
> Stefano Condotta wrote:
>> Hello Aladin,
>>
>> I'm afraid your final formula produces a curcular reference error.
>> $C$3 cannot be in the formula. Column B works well. I'm trying to get it
>> to work. Thanks.
>> "Aladin Akyurek" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Let A2:A25 be your first column range with A2 housing a label.
>>>
>>>In B1 enter: 0
>>>
>>>In B2 enter: Idx [ which is just a label ]
>>>
>>>In B3 enter & copy down:
>>>
>>>=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")
>>>
>>>In C1 enter:
>>>
>>>=LOOKUP(9.99999999999999E+307,B1:B25)
>>>
>>>In C2 enter: No Blanks List
>>>
>>>In C3 enter & copy down:
>>>
>>>=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>>>
>>>From C3 on, you'll have the desired list with no interspersed blank/empty
>>>cells.
>>>
>>>Chaturanga wrote:
>>>
>>>>Hello,
>>>>
>>>>
>>>>I'm looking for a worksheet formula that will produce a list of no
>>>>blank values in a third column. First column contains the text values
>>>>(30 rows) and they already listed with no blanks. The second column has
>>>>boolean TRUE or FALSE values corresponding with the values in the first
>>>>column. I'm looking for a formula that will list all first column
>>>>values with corresponding second column values that are FALSE in the
>>>>third column with no blanks.
>>>>
>>>>Thanks
>>>>
>>
>>

[Aladin Akyurek]

I see what happened. The formula should be:

=IF(ROW()-ROW($C$3)+1<=$C$1,LOOKUP(ROW()-ROW($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")

Stefano Condotta wrote:
> The formula:
>
> =IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>
> poduces a circular reference if entered in cell C3 (as I read your
> instructions). I did not adapt to my situation as I tested it out on a clean
> worksheet first.
>
> Regards,
> Stefano
>
> "Aladin Akyurek" <[email protected]> wrote in message
> news:[email protected]...
>
>>Stefano,
>>
>>That should not happen. Would you post the formulas as you adjusted them
>>to your data?
>>
>>Stefano Condotta wrote:
>>
>>>Hello Aladin,
>>>
>>> I'm afraid your final formula produces a curcular reference error.
>>>$C$3 cannot be in the formula. Column B works well. I'm trying to get it
>>>to work. Thanks.
>>>"Aladin Akyurek" <[email protected]> wrote in message
>>>news:[email protected]...
>>>
>>>
>>>>Let A2:A25 be your first column range with A2 housing a label.
>>>>
>>>>In B1 enter: 0
>>>>
>>>>In B2 enter: Idx [ which is just a label ]
>>>>
>>>>In B3 enter & copy down:
>>>>
>>>>=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")
>>>>
>>>>In C1 enter:
>>>>
>>>>=LOOKUP(9.99999999999999E+307,B1:B25)
>>>>
>>>>In C2 enter: No Blanks List
>>>>
>>>>In C3 enter & copy down:
>>>>
>>>>=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>>>>
>>>
>>>>From C3 on, you'll have the desired list with no interspersed blank/empty
>>>
>>>>cells.
>>>>
>>>>Chaturanga wrote:
>>>>
>>>>
>>>>>Hello,
>>>>>
>>>>>
>>>>>I'm looking for a worksheet formula that will produce a list of no
>>>>>blank values in a third column. First column contains the text values
>>>>>(30 rows) and they already listed with no blanks. The second column has
>>>>>boolean TRUE or FALSE values corresponding with the values in the first
>>>>>column. I'm looking for a formula that will list all first column
>>>>>values with corresponding second column values that are FALSE in the
>>>>>third column with no blanks.
>>>>>
>>>>>Thanks
>>>>>
>>>
>>>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

[Stefano Condotta]

Excellent! Thanks.

"Aladin Akyurek" <[email protected]> wrote in message
news:[email protected]...
>I see what happened. The formula should be:
>
> =IF(ROW()-ROW($C$3)+1<=$C$1,LOOKUP(ROW()-ROW($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>
> Stefano Condotta wrote:
>> The formula:
>>
>> =IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>>
>> poduces a circular reference if entered in cell C3 (as I read your
>> instructions). I did not adapt to my situation as I tested it out on a
>> clean worksheet first.
>>
>> Regards,
>> Stefano
>>
>> "Aladin Akyurek" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Stefano,
>>>
>>>That should not happen. Would you post the formulas as you adjusted them
>>>to your data?
>>>
>>>Stefano Condotta wrote:
>>>
>>>>Hello Aladin,
>>>>
>>>> I'm afraid your final formula produces a curcular reference error.
>>>> $C$3 cannot be in the formula. Column B works well. I'm trying to get
>>>> it to work. Thanks.
>>>>"Aladin Akyurek" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>
>>>>>Let A2:A25 be your first column range with A2 housing a label.
>>>>>
>>>>>In B1 enter: 0
>>>>>
>>>>>In B2 enter: Idx [ which is just a label ]
>>>>>
>>>>>In B3 enter & copy down:
>>>>>
>>>>>=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$1:B2)+1,"")
>>>>>
>>>>>In C1 enter:
>>>>>
>>>>>=LOOKUP(9.99999999999999E+307,B1:B25)
>>>>>
>>>>>In C2 enter: No Blanks List
>>>>>
>>>>>In C3 enter & copy down:
>>>>>
>>>>>=IF(ROW()-($C$3)+1<=$C$1,LOOKUP(ROW()-($C$3)+1,$B$2:$B$25,$A$2:$A$25),"")
>>>>>
>>>>
>>>>>From C3 on, you'll have the desired list with no interspersed
>>>>>blank/empty
>>>>
>>>>>cells.
>>>>>
>>>>>Chaturanga wrote:
>>>>>
>>>>>
>>>>>>Hello,
>>>>>>
>>>>>>
>>>>>>I'm looking for a worksheet formula that will produce a list of no
>>>>>>blank values in a third column. First column contains the text values
>>>>>>(30 rows) and they already listed with no blanks. The second column
>>>>>>has
>>>>>>boolean TRUE or FALSE values corresponding with the values in the
>>>>>>first
>>>>>>column. I'm looking for a formula that will list all first column
>>>>>>values with corresponding second column values that are FALSE in the
>>>>>>third column with no blanks.
>>>>>>
>>>>>>Thanks
>>>>>>
>>>>
>>>>
>>
>
> --
>
> [1] The SumProduct function should implicitly coerce the truth values to
> their Excel numeric equivalents.
> [2] The lookup functions should have an optional argument for the return
> value, defaulting to #N/A in its absence.