Creating a Flag based on cell value

[Dan G.]

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

 

[Brian Taylor]

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.

 

[Guest]

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

then drag fill down the column

would also work.

 

[Brian Taylor]

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