Formula to ADD to last digit depending on scenario. Hope you can help me thanks!

wedding

wedding

Board Regular
Joined
Jun 12, 2010
Messages
127
15ft92w.jpg


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!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
Well, I've come up with something that works :)
*Now, I'm not the most efficient coder, and this is technically my first (useful) UDF, so if anyone has a more efficient solution, please go ahead and post below.
Code: 
Function counter(myRange As Range)    newRange = Right(myRange.Address, 4)
    myVar = Range(newRange).Offset(-2, 0).Value & Range(newRange).Offset(-1, 0).Value & Range(newRange).Value
    Select Case myVar
    Case "AAA", "BAA"
        counter = Range(newRange).Offset(-1, 1).Value + 1
    Case "AAB", "BAB"
        counter = ""
    Case "ABB", "ABA"
        counter = Range(newRange).Offset(-2, 1).Value + 1
    Case "BBA"
        counter = 1
    Case "BBB"
        If Range(newRange).Offset(-1, 1).Value > 0 Then
            counter = ""
        Else
            counter = 1
        End If
    Case Else
        Select Case Range(newRange).Offset(-1, 0).Value & Range(newRange).Value
        Case "AA"
            counter = 2
        Case "AB"
            counter = ""
        Case "BB", "BA"
            counter = 1
        Case Else
            If Range(newRange).Value = "A" Then
                counter = 1
            Else
                counter = ""
            End If
        End Select
    End Select
End Function
Some things you should know, it errors the first line (or two) if the data starts in the 2nd row. So to avoid any errors, I would suggest starting the data in row 3 or below.
the syntax of the equation is =Counter(A3) and then drag it down.
Cheers,
 
Upvote 0
Thanks for your help! Appreciate it! Sorry for being a newbie but how to do I use this UDF?
 
Upvote 0
While in Excel, press ALT + F11
the Visual Basic for Applications window will pop up.
Insert > Module > Then paste the code above in there.
Return to the worksheet, then use the counter function as you would any other function. =Counter(A3) & Drag down
 
Upvote 0
Thanks I will give it a test when I am home. On a side note, does the results of your formula fits the screenshot I posted?

Also thanks again for helping me with your precious time:)
 
Upvote 0
You could also do it with an Array Formula. Assuming your data started in A3:

B3: =IF(AND(COUNTIF(A2:A3,"=B")=2,B2=""),COUNTIF(INDIRECT("A"&MAX((A$1:A1="B")*(A$2:A2="B")*ROW(A$2:A2),1)&":A"&ROW()),"=A")+1,IF(A3<>"B",COUNTIF(INDIRECT("A"&MAX((A$3:A4="B")*(A$2:A3="B")*ROW(A$3:A4),1)&":A"&ROW()),"=A"),""))

confirm with Ctrl+Shift+Enter (NOT just Enter) and fill down.

This also has been confirmed to match your pattern.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Automating copying of columns within same sheet in excel using VBA.
Replies
1
Views
101
MountainFog
M
B
  • Question
European Roulette Bet Maker
Replies
3
Views
432
lrobbo314
lrobbo314
Kellie220
  • Solved
Formula for calculating different dates/ VBA
Replies
3
Views
131
awoohaw
awoohaw
L
  • Question
Step 1: Can you Loop Through Multiple Excel worksheets in one workbook (Copy and paste all to new worksheet or Workbook)
Replies
9
Views
395
Micron
M
S
  • Question
Unexpected and inconsistent error with ranking rows based on criteria
Replies
2
Views
96
strugglingaccountant
S

Forum statistics

Threads
1,214,632
Messages
6,120,655
Members
448,975
Latest member
sweeberry

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top