I have a report in Excel 2010 which summarises costs against certain activity charge codes. The charge codes for this particular project are sequential and always 8 alpha characters. They all start with the same 4 characters and then end with AAAA, moving on to AAAB, AAAC etc. I also use this list to indicate which activity charge code is needed next in the sequence and so far this works.
The summary list has a column that pulls the last character for each of the activities, populating a range G2:G26. I then use the following formula to determine what character I need next:
entered as an array formula.
My list of last characters is not in alphabetical order so it isn't as simple as looking at the last one in the list. The current list has reached Y so the next activity to be raised will end in AAAZ. Thats fine, my formula currently returns Z as the correct answer.
However when I go beyond Z, the next activity will have to end AABA, then AABB etc.
A sample of the set up is attached. You will see from the sample that some tasks have multiple phases (preparation and execution (P / E)) and each phase has a new activity code, hence why they are not in alphabetical order. Some tasks were raised in original group and then others have been added.
The formula that I am trying to update is highlighted. I am happy to change the formulas to pull out the existing suffixes if required, hence why I have supplied the full suite of data as best I can.
At the moment I can do the increment simply but as this project expands it will become less simple.