Hi,

I have a schedule for shifts, and the teams are assigned these numbers:

1
2
3
4
5
9
11
13
14
15
17

I need to display in a cell (or list of cells) which team has not been scheduled that day. My spreadsheet is set up as a workshift calendar (with 50+ possible assignments aka rows), so I am thinking a formula would be best so that I could copy it over for each day of the week. Can anyone help? The first range of cells that would need to be searched would be B4:B64, with results of missing search displayed in B65(through B75 if need be).

Hi,

This is possible with formulas, but it's not an efficient way to do it. Your best bet is to write a small VBA procedure which will calculate the missing values and put them into the relevant range. Have you worked with VBA before?

I am familiar with VBA, I just wasn't aware that I could use it produce results in multiple columns. Any assistance you could provide would be appreciated. I am self taught on most code, so I am learning something new everyday :-)

Sure, please can you attach a sample workbook so we're working from the same starting point?

I appreciate your assistance!

I've attached a formula solution.

In cells A67:A77 I've listed the distinct numbers which teams can be assigned per post #1.

Then I put this formula in B67:
And then copied it down to B77 and across the columns for each date. This formula checks the schedule and counts how many times the "required number" appears. If it doesn't appear then it returns the required number, else it returns an empty string. This formula allows for a single space and comma separated lists within the cells (as per your attachment) so, if it was looking for the number 17, then all of these would be recognised (for the purposes of this post, I used _ to represent a space):
If you want me to explain the formula in more detail then just ask.


This leaves gaps in the blocks of cells where numbers have been found (see range B67:AC77). I wasn't sure if that was acceptable or not, so I hid them and then added another block underneath (see range B81:AC92) where the gaps are removed by using this formula:
The values in A81:A92 are simply indexes. Obviously you can hide rows / format them however you want.

I'm not sure why you didn't have 11 listed as missing for 28th and 29th.

Thanks so much! The attachment won't unzip for me so I can't view the document, but I am following what you did for the formula and it makes total sense. I would not have thought to account for spaces or commas. I am trying to understand what you did after you hid the gaps, but I am sure once I plug the formula in it will make sense. Looks like I need to teach myself more about the IF and COUNTIF statements. (I am not sure why 11 was omitted either, but that was why we needed a formula!)

Thanks SO much, and if you are ever in the San Francisco area, I owe you a pint!

Glad it helped.

The index values in A81:A92 I referred to are just the numbers 1,2,3,4,5,6,7,8,9,10,11,12 (one number per cell)