Is there a function/ code that can look down a column and apply a formula
when there is a change in data. i know there is the subtotal function but
this does not work for my requirement. Using the following data I would want
to automatically look at where the data changes (in column A) and apply a
formula in another column (say Column B) for the same row. I would therefore
expect to apply the formula as per the following example to cells B1, B4, B5
and B9

Col A
row 1 AAA
row 2 AAA
row 3 AAA
row4 BBB
row5 CCC
row6 CCC
row7 CCC
row8 CCC
row9 DDD