Creating a Flag based on cell value

Hello. I'm trying to create a Function based on the cell values within a
range to create a flag (1 - on or 0 - off) in another.

The problem is the same value when it repeats has to have a different
flag value. For example

Range-x Range--y
Key -----> 1
Hello -------> 1
Bye --------> 1
Key --------> 0
Yes --------> 0
No ---------> 0
Key --------> 1
This --------> 1
That --------> 1
Key --------> 0
Fix --------> 0

So the flag needs to alternate designation off of a particular value
when it appears in the range and alternate in groups.

When it first appears, everything underneath it is a "1". When it
appears again as a trigger, everything underneath it is a "0" until it
appears again, and so on. I hope this makes sense.

Any help here would be greatly appreciated.

Thanks

Dan

*** Sent via Developersdex http://www.developersdex.com ***

You shouldn't need a UDF for that. Try this formula and drag down:

=IF(MOD(SUM(($A$5:A5="Key")*1),2)=1,"Yes","No")

It is an array formula so be sure to use ctrl shift enter.

in cell Y1
=MOD(COUNTIF($X$1:X1,"Key"),2)

then drag fill down the column

would also work.

--
Regards,
Tom Ogilvy

"Brian Taylor" wrote:

> You shouldn't need a UDF for that. Try this formula and drag down:
>
> =IF(MOD(SUM(($A$5:A5="Key")*1),2)=1,"Yes","No")
>
> It is an array formula so be sure to use ctrl shift enter.
>
>

I've been out done! Nicely done Tom. I guess I get stuck on array
formulas sometimes. Better to avoid them if you can.