Creating a discrepancy for schedule

Occasional Contributor

Hello, I am working on creating a user friendly schedule rotating schedule. 


I am trying to see how I can create a formula or a way to indicate when I am missing a "rotation"

For example, in the following image. I am missing "DU" and "C".


Any way I can do this?Capture.PNG


The numbers next to the Key is how many should be on the hour. I hope this makes sense. 


Thanks for all your help!

16 Replies


Why is C missing? It appears to be in row 4.

I meant to take it off as an example. But in this case it would only be the DU


Would it be possible to fill all the colored cells with the appropriate letter? Otherwise, you'd need complicated VBA code!

@Hans Vogelaar 

No because not every spot has to be filled, it is an hourly schedule. 



I meant only the colored cells, for example RG not only in cell AL5, but also in AM5 to AO5.


Here is an illustration of what I meant. The formula in AL29, confirmed with Ctrl+Shift+Enter, is






As variant



=TEXTJOIN(", ",1,

@Sergei Baklan 

I suspect that the coloring matters, and that the MP in AN8 (with AN8:AO8 colored) and in AL17 (with AL17:AM17 colored) count as 2 * 0.5 = 1

@Hans Vogelaar 

Oh, yes, I missed that

Neither seemed to work when I applied it. Should I be applying something elsewhere?


It might help if you attached a sample workbook without sensitive/proprietary data.

@Hans Vogelaar 

This is the sample of the schedule. 


The only ones that are truly important to know that are covered are the ones that are in the picture in the initial post. 




The screenshot in your first post had numbers next to the legend keys.

best response confirmed by mvargas (Occasional Contributor)


See the attached version.


It's worth to mention on which version of Excel you are. Most probably you need to use array formulas (Ctrl+Shift+Enter)

Anyway, @Hans Vogelaar formula shall work.

Thank you for all your help! It worked!