Search for value, and then copy value in adjacent column

Hello

I have a column with many different 4 letter values in column B (about 6000 rows all up), in column A I have cases which can either be A or B, I am looking for a formula in column C that searches for one particular 4 letter value in col B, and then copies "1" into all the rows above where the case is the same as the case adjacent to the target 4 letter value, and copies "2" into all the rows above where the case is different to the case next to the adjacent target 4 letter value. Then, search for the nexy time that the 4 letter value appears, and repeat the step working back up the list until the last time that the target 4 letter value appeared.

I apologise for the confusing description, I have attached an example spreadsheet which should make things clearer.

To state simply, find everytime where the 4 letter value "YEST" appears in col B, check if it is case A or B in the adjacent cell in col A, and then place a "1" in col C for each case above the "YEST" value that matches the case next to the "YEST" value, and a "2" next to each case that does not match the case appearing next to the "YEST" value. Once this is done, move onto the next "YEST" value down and repeat the process (bearing in mind that a different case could appear next to the "YEST" value).

I understand this quite a complex problem, but any assistance would be gratefully received

T15K

Thank you for the excellent solution.

Could I ask you to add another layer of complexity to the formula? Basically, I would like to include a second target code in Col B. For example, if "TMRW" appears, then repeat the process described earlier with the values 3 and 4. So now if "YEST" appears, we want to copy up 1 or 2, depending on whether the case matches Column A, and if "TMRW" appears, do the same thing but with 3 or 4, depending on whether the case matches. These values would be copied up until either "YEST" or "TMRW" appears again.

Is it possible to add this extra criteria into the formula?

Many thanks for your assistance so far

T15K

I have attached a sample spreadsheet. Basically what I am after is similar to the first formula, however there are now two possible target codes "YEST" and "TMRW", each with different values (1 and 2, or 3 and 4, respectively). Otherwise, same process, copy the values upwards for case A and B, depending on whether it matched the target word.

Thanks
T15K

Great, that works perfectly. Thank you very much.