Hi Guys, I need help in getting a formula right. I have attached a picture, FIRST column containing A's and B's. While SECOND column containing some numbers. I need a formula where the SECOND column can calculate get those numbers. I will write it as easy as possible to understand. Here are the rules for the SECOND column: *Step 1: If A happens in the FIRST column, we will write a "1" in the SECOND column. Step 2: For every A that happens we will +1 to the SECOND column column of the last digit. So in the next row another A happens so we +1 and the result will be "2" in the SECOND column. Step 3: Every time B happens we do two things. Either we wait for two B to happen then we +1 to the latest digit. So once we got a B there is a blank as we are waiting for either two B or one B and one A. Step 4: As you can see, another B appeared so there are two consecutive Bs, therefore we +1 to the latest digit, giving us a "3" now. *Every time there is two consecutive Bs, the latest digit will reset to 1 and we start from Step 1. Following there is a A so the count in SECOND column is 1 again. Next we have a B, here we can look at Step 3, whenever we got a B we wait for either two consecutive B's or a B followed by A, as you can see here A appeared after this B. From here we can look at Step 2, "For every A that happens we will +1 to the SECOND column of the last digit" so now the digit will be +1 giving us a "2". --------------------------------------------- The rules are basically these, hope you guys can help me with a formula that I can copy and paste down the SECOND column. I hope I can write this clearer, if you have any questions I will gladly answer. Hope this is not too much to ask its for a project of mine thanks!

This question generated 10 answers. To proceed to the answers, click *here*.

This thread is current as of November 27, 2014.

For more resources for Microsoft Excel:

This article includes the following tags:

- Excel
- Microsoft Excel