# Thread: Find Missing Number fron Random List

1. Registered User
Join Date
Dec 2009
Posts
39

## Unanswered: Find Missing Number fron Random List

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

2. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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?

3. Registered User
Join Date
Dec 2009
Posts
39
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 :-)

4. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
Sure, please can you attach a sample workbook so we're working from the same starting point?

5. Registered User
Join Date
Dec 2009
Posts
39
Last edited by SRR; 07-11-11 at 19:13.

6. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511
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:
Code:
`=IF(SUM(COUNTIF(B\$4:B\$64,{""," ","*,","*, "}&\$A67&{"";" ";",*";" ,*"})),"",\$A67)`
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):
Code:
```17
17_
17,
17_,
_17
_17_
_17,
_17_,
,17
,17_
,17,
,17_,
,_17
,_17_
,_17,
,_17_,```
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:
Code:
`=IF(COUNT(B\$67:B\$77)>=\$A81,SMALL(B\$67:B\$77,\$A81),"")`
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.
Last edited by Colin Legg; 07-12-11 at 18:15.

7. Registered User
Join Date
Dec 2009
Posts
39
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!

8. Registered User
Join Date
Sep 2008
Location
London, UK
Posts
511