Different if statement based on the first ID in a block of ID's in Excel (VBA?)

So basically I have an excel sheet featuring a number of different id's, dates, and a column where I would like to specify beginning middle or end in a 3rd column depending on the FIRST ID date. However this should only apply to the block of the same ID. When the next ID passes through, the date of the first ID in the block will determine the rest of the 3rd column, and so on and so forth.

The issue is much better understood when looking at the dataset.

ID      DATE      formula_col
10R46   10/8/2011
10R46   2/18/2012
10R46   6/30/2012
10R47   2/4/2010
10R47   5/16/2010
10R47   8/8/2010
10R47   12/11/2010
10R47   1/4/2011
10R48   6/26/2011
10R48   9/11/2011
10R48   1/29/2012
10R48   4/20/2012
10R48   7/8/2012

As you can see here. There is a block of consecutive ID's and I need the 'formula_col" to read beginning for the first date, and the rest of the dates would follow the following rule:

The first month is beginning, next is middle, next is end, then it repeats.

So if the first month was February (2), then the formulas for these ID's would be: February: Beginning March: Middle April: End May: Beginning June Middle July: End August: Beginning September: Middle October: End November: Beginning December: Middle January: End And any could appear, not necessarily beginning, middle, end in that order. But they will always be in date order, no going back in time and such until the ID is over.

So for the aforementioned dataset, it would be:

ID      DATE      formula_col
10R46   10/8/2011 Beginning
10R46   2/18/2012 Middle
10R46   6/30/2012 End
10R47   2/4/2010  Beginning
10R47   5/16/2010 Beginning
10R47   8/8/2010  Beginning
10R47   12/1/2010 Middle
10R47   1/4/2011  End
10R48   6/26/2011 Beginning
10R48   9/11/2011 Beginning
10R48   1/29/2012 Middle
10R48   4/20/2012 Middle
10R48   7/8/2012  Middle

To clarify: The first date of the ID will determine which months are Beginnings, Middles, and Ends.

Here is code I created if the first month is January, April, July, or October. But this isn't exactly what I want, just a start.

=IF(OR(MONTH(B2)=1,MONTH(B2)=4, MONTH(B2)=7,MONTH(B2)=10),"Beginning",IF(OR(MONTH(B2)=2,MONTH(B2)=5, MONTH(B2)=8,MONTH(B2)=11),"Middle",IF(OR(MONTH(B2)=3,MONTH(B2)=6, MONTH(B2)=9,MONTH(B2)=12),"End",NA)))

So the tricky part is the start of the month pattern depends on the first entry of the unique ID. Then it begins following the rule which only applies to this ID. When the next ID appears, the rule resets based on the date of the first ID. They will always be consecutive though I would prefer not to rely on that.

VBA solutions welcome

Use INDEX/MATCH to return the first date and subtract that month and year from the current, then use MOD to return the relative month in that quarter.

Then passing that to CHOOSE to return the correct text:

=CHOOSE(MONTH(B2)-MONTH(INDEX(B:B,MATCH(A2,A:A,0))),3)+1,"Beginning","Middle","End")

But this only works if the data is always sorted like you have it: