lookup to return true() values only and list

[Robbie]

Hi
i have column a1 : a10 with true() or false() values. Column b1 : b10 I have
text eg A, B C - J.

In column C, if a1 : a10 is true() then b1 : b10.

The problem I have is if only 3 values are true() I need to have the 3
values in C1, C2, C3

ie if a3 = true() then in c1 "C", a10 = true() then c2 = "J"

Hope you can help

Regards

 

[Gary''s Student]

See if I understand. Column A has TRUE/FALSE. Column B has text. You want
column C to have the column B values, but only for TRUE:

answer text
TRUE Cordelia Cordelia
FALSE Ophelia Bianca
TRUE Bianca Desdemona
FALSE Cressida Portia
TRUE Desdemona Mab
FALSE Juliet Perdita
TRUE Portia Cupid
FALSE Rosalind Francisco
TRUE Mab
FALSE Belinda
TRUE Perdita
FALSE Puck
TRUE Cupid
FALSE Miranda
TRUE Francisco
FALSE Ariel

1. turn-on AutoFilter on column A and select TRUE
2. copy the visible cells in column B
3. turn-off the AutoFilter
4. paste in C1

 

[Robbie]

Thank you

See if I understand. Column A has TRUE/FALSE. Column B has text. You want
column C to have the column B values, but only for TRUE:

answer text
TRUE Cordelia Cordelia
FALSE Ophelia Bianca
TRUE Bianca Desdemona
FALSE Cressida Portia
TRUE Desdemona Mab
FALSE Juliet Perdita
TRUE Portia Cupid
FALSE Rosalind Francisco
TRUE Mab
FALSE Belinda
TRUE Perdita
FALSE Puck
TRUE Cupid
FALSE Miranda
TRUE Francisco
FALSE Ariel

1. turn-on AutoFilter on column A and select TRUE
2. copy the visible cells in column B
3. turn-off the AutoFilter
4. paste in C1

 

[Robbie]

Hi Gary

I have tried as explained, problem is that when I change the values in
column a, I have to re select true() in auto filter. I am trying to get this
to work automatically

Hope you can help

 

[Gary''s Student]

This can be automated in two different ways:

1. a VBA macro
or
2. formulas with a helper column

The formula method is well-described in:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx