Building list based on condition

H

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
 
R

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
 
M

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:
(rest are blanks:"")
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top