Creating dummy values 1

[mchoss]

I am certain there is a simply way to do this but as yet, none of the excel functions i have looked up seem to do exactly what i am looking for:

Essentially i want to create a dummy value for each string in a list according to whether it is in some subset of this list. For example ...

If the subset is

red
blue

then i would like the following output

Value Dummy
Green 0
Yellow 0
Red 1
White 0
Blue 1
Black 0

Note, the list is long so ideally i would like to provide a cell range as the argument in which to search.

Any ideas?

Thanks in advance

 

[mintjulep]

Seems like an appropriate combination of IF, VLOOKUP and ISERR should do the trick.

 

[SkipVought]

Hi,

I'd suggest the MATCH function
[tt]
=if(iserror(match(A1,YourRedGreenRange,0)),0,1)
[/tt]


Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[KenWright]

Assuming your data is in A1:A1000 (Header in Row 1), and your subset range was in E1:E10, then in cell B2 put the following and copy down.

=COUNTIF($E$1:$E$10,A2)

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mchoss]

Indeed, all good ideas, thank to everyone who replied.

I had some success with the IF MATCH combination before i read these replies, but as you point out, it does leave you with the problem of having to clear up the #N/A ... i shall try out this ISERR and/or the COUNTIF functions.

Much appreciated

 

[mchoss]

Ken,

The COUNTIF function is clearly the simplest and most succinct solution to my particular problem.

Thanks again

 

[KenWright]

You're welcome. I like it because it gets rid of the need to account for #NAs

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------