Creating a discrepancy for schedule

[mvargas]

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?

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

Thanks for all your help!

[#2]

@mvargas 

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

[#3]

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

[#4]

@mvargas 

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

[#5]

@Hans Vogelaar 

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

[#6]

@mvargas 

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

[#7]

@mvargas 

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

=TEXTJOIN(",",TRUE,IF(AO21:AO26,AL21:AL26,""))

 

[#8]

@mvargas 

As variant

with

=TEXTJOIN(", ",1,
  IF(COUNTIFS(AL$3:AO$20,AL$25:AL$30)<$AM$25:$AM$30,
     AL$25:AL$30,
     ""))

[#9]

@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

[#10]

@Hans Vogelaar 

Oh, yes, I missed that

[#11]

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

[#12]

@mvargas 

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

[#13]

@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. 

[#14]

@mvargas 

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

[#15 · Accepted solution]

@mvargas 

See the attached version.

[#16]

@mvargas 

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.

[#17]

Thank you for all your help! It worked!