Multiple recurring events in Rows & Columns

[cyrilbrd]

I would like to get your help on the following:
I would like to create a calendar within excel that would be linked to a series of recurring events.
Those events are happening every 15 days, and expected to occur for a period of up to 15 months (so 30 times in a row).
We have about 1000 events, each named with an ID code (a01,a02,a03...)
We have a database that contains the id code, the date of first manipulation and the recurring dates after that, this file is updated daily, as some schedule might have been moved ±1 day as it is meant to be flexible.
The difficulty being that a single day may have up to 50 events, hence 50 ID code.

The database (Sheet DB) is organized as follows first row for the headings, data starting from row 2 till row 1205.
Column A is blank
Column B is for the Department ID (Drop Down Menu).
Column C is for the ID code
Column D for the first date (encoded by coordinator).
Column E and forth (AF) for the due dates (formula based on column B).
Now for some instance we have events up to column CC (schedule is flexible and prone to change).
Due dates are linked and will update themselves automatically if any previous date is modified.

For the result (Sheet Cd), the Coordinator needs to see for a specific date all ID codes due for manipulation.

Cd is arranged as follows:
Column A is blank
Column B is the date range that is from 7 days prior today() (row 2) till today()+30 days (row 39).
Column C to BN is supposed to allow the coordinator to see a whole month schedule, due events of manipulation, and monitor past week schedule (in case of delayed manipulation as I stated a possibility of schedule being moved). If this would work, the coordinator would need not update the summary but merely update the database.
A conditional formatting with the formula $A2=TODAY() as condition in order to highlight the present day (faster to spot) is used.
The formula used in C2 is as follows:
=IFERROR(INDEX(DB!$B$2:$B$459, SMALL(IF(Cd!$B2=DB!D$2:D$459, ROW(DB!D$2:D$459)-MIN(ROW(DB!D$2:D$459))+1, ""), ROW($A$1))),"")
I stopped at row 459 here, but eventually it should go to row1205.
Above formula being an array is entered as “command+ return” (using a mac, excel v14.1.4).

I was hoping to have each corresponding ID code returned for a specific date regardless of their position in the table DB. DB is sorted per ID code.

Right now the formula is working, as long as we have NO duplicated in Sheet Cd Column D. Also the results are scattered along the rows, and will require scrolling.

I fear my explanations may drown a few. Please let me know if more detail (or less) are needed.

Thank you in advance.

Cheers,

Cyril.

PS. Formula would be the best, VBA is possible only in v14, and some of our mac are still using excel 2008 hence no VBA.

 

[cyrilbrd]

Temporarily solved with pivot and merge.

Still hoping to find killer formula to retrieve data from both row and column.