Building list based on condition

[Henrik]

Does anyone have any suggestions as to how to build a function that
automatically selects the next observation with a particular value assigned
to it?

For instance, I have a dataset that looks like this (where column A contains
a list of names and column B contains a corresponding value/toggle that can
be either TRUE or FALSE):

Apple TRUE
Banana TRUE
Lemon TRUE
Orange FALSE
Grapefruit TRUE


As the desired function is dragged down, it will only select observations
with a TRUE VALUE in column B. The result would be the following list of
names. If the Lemon toggle was changed to FALSE, the list would no longer
contain Lemon.


Apple
Banana
Lemon
Grapefruit



Thanks,
Henrik

 

[RagDyer]

If I understand what you're looking for, enter this in C1, and drag down to
copy as needed:

=IF(B1,A1,"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Does anyone have any suggestions as to how to build a function that
automatically selects the next observation with a particular value assigned
to it?

For instance, I have a dataset that looks like this (where column A contains
a list of names and column B contains a corresponding value/toggle that can
be either TRUE or FALSE):

Apple TRUE
Banana TRUE
Lemon TRUE
Orange FALSE
Grapefruit TRUE


As the desired function is dragged down, it will only select observations
with a TRUE VALUE in column B. The result would be the following list of
names. If the Lemon toggle was changed to FALSE, the list would no longer
contain Lemon.


Apple
Banana
Lemon
Grapefruit



Thanks,
Henrik

 

[Max]

One way to set it up

Assuming the data below is
in Sheet1, cols A and B, in row2 down

Apple TRUE
Banana TRUE
Lemon TRUE
Orange FALSE
Grapefruit TRUE

Use an adjacent helper col C

Put in C1: =Sheet2!A1

Put in C2: =IF(B2="","",IF(B2=$C$1,ROW(),""))

Copy C2 down to say, C1000
to cover max expected data in cols A and B

In Sheet2
-----------
Let's make a DV to select TRUE or FALSE in A1

Select A1
Click Data > Validation
Settings:
Select under "Allow:" : List
Put in "Source:" : TRUE, FALSE
Click OK

Now put in A2:

=IF(ISERROR(SMALL(Sheet1!C:C,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!C:C,ROWS($A$1:A1)),Sheet1!C:C,0)))

Copy A2 down to A1000
(cover the same range size in Sheet1)

Col A will return all the items from Sheet1
where col B's toggle is set to
what's chosen in the DV in A1 (in Sheet2)

For the sample data in Sheet1,
assuming the DV in A1 is set to: TRUE
you'll get:

Apple
Banana
Lemon
Grapefruit

(rest are blanks:"")

If A1 is set to: FALSE, you'll get:

Orange

(rest are blanks:"")

 

[Aladin Akyurek]

Let A2:B7 house the sample you provided with labels in A2:B2...

{"Item","Flag";
"Apple",TRUE;
"Banana",TRUE;
"Lemon",TRUE;
"Orange",FALSE;
"Grapefruit",TRUE}

which says: A2 = Item, B2 = Flag, A3 = Apple, B3 = TRUE, etc.

C2 must house a 0.

In C3 enter & copy down:

=IF(B3=TRUE,LOOKUP(9.99999999999999E+307,$C$2:C2)+1,"")

In D1 enter:

=LOOKUP(9.99999999999999E+307,C:C)

In D2 enter: Result List

In D3 enter & copy down:

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

The area in from D3 on will show the desired result list.