SOLVED

# Creating a discrepancy for schedule

Occasional Contributor

# Creating a discrepancy for schedule

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.

16 Replies

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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,""))

# Re: Creating a discrepancy for schedule

As variant

with

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

Oh, yes, I missed that

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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

# Re: Creating a discrepancy for schedule

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.

# Re: Creating a discrepancy for schedule

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

best response confirmed by mvargas (Occasional Contributor)
Solution

# Re: Creating a discrepancy for schedule

See the attached version.

# Re: Creating a discrepancy for schedule

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.

# Re: Creating a discrepancy for schedule

Thank you for all your help! It worked!