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

Question

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

In your sample output, it looks like only the first ID follows the pattern you describe ("The first month is beginning, next is middle, next is end, then it repeats.") Why are there three "Beginnings" for 10R47, two for 10R48 and three "Middles" for 10R48? — cybernetic.nomad
@ScottCraner yes, always consequetive and the date only moves forward — Johnny Thomas
@cybernetic.nomad The pattern is based on the first ID date. So it isn't the dates that follow rather it is if the dates that follow fall into the categories. What I mean is if February is the first date for the ID, If a March appears in the ID it has to be middle, if an April appears it has to be end, if May appears it has to be beginning, and so on. When the next ID hits, it starts with begiining as the first date and follows through accordingly — Johnny Thomas
Then why is august the beginning and then December middle, by your description it should be beginning again. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2019-06-27T15:26:11

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:

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

1 Answers