Return a TEXT value if certain TEXT is missing

[steve89]

I really need help with this formula. This is what I'm looking for

I'm writing a schedule but want to make it very automated for the managers. I have the following positions on my schedule: AM 1, AM 2, AM 3, AM 4, PM 1, PM 2 & PM 3. These positions are on everyday. In that specific space sequence. I want the sheet to know if column d9:d23 were missing any of those shifts to automatically return that exact missing shift. I know how to use in conditional formula but don't want color coding just want it to return the missing shift if these are suppose to be all the shifts for 1 day.

Thank you kindly all love this site great help and knowledgeable smart individuals

 

[steve89]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

sorry had to learn how to use HTML maker there is the revised paste - missing shifts is great function but if you see on cell E20 and E31 there are duplicates - can i add to those formulas that show the same two shifts as there shouldnt be the same two shifts?

 

[Peter_SSs]

Re: Need help..Want to return a TEXT value if certain TEXT is missing

Sorry, I still don't follow. In post 47 you said the problem was if there are empty cells its returns "missing 0". I can't see any of that in your sample post & in any case now you seem to be describing something different.

Further, the formulas shown refer to data on a sheet called 'Employee Data', which I cannot see.
Remember also that it is nearly two years since I had anything to do with this so it is completely unfamiliar to me again now.

 

[steve89]

so sorry here is the other tab - so the two things are if there is no information in "Employee Data" cells A13:A29 which there isnt instead of putting " ' " to avoid seeing the erro "missing o" can there be a fix to that so its not saying "missing o" when the cell is empty and if there is a duplicate shift in the last example AM 1 listed twice is there a formula to show that shift is a duplicate and needs to be removed while keeping the "missing shift" alert?

Excel Workbook
	A
2	AM COOK
3	PM COOK
4	PR COOK
5	AM 1
6	AM 2
7	AM 3
8	AM 4
9	PM 1
10	PM 2
11	PM 3
12	PM 4
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Employee Data

 

[steve89]

example of the "missing o" issue

Excel Workbook
	D	E
20	OFF	AM 1
21	AM 1	AM 2
22	VAC	VAC
23	AM 4	AM 3
24	AM 3	OFF
25	N/A	AM 4
26	PM 1	PM 1
27	AM 2	OFF
28	PM 3	PM 2
29	OFF	PM 3
30	OFF	PM 4
31	PM 4	AM 1
32	PM 2	OFF
53
54	Missing: 0	Missing: PM COOK, 0
55
MASTER UNIVERSAL SCHEDULE

 

[steve89]

The question asking to remove the "missing o" is solved. But i still had a question as you can see on cells E20 and E31 there are duplicate shifts purposely done to try and figure out a formula on a separate cell that ensures the same shifts aren't listed twice based off of the data in "Employee Data" A2:A29. The formula needs to check E20:E32 (for this example) and ensure no duplicate shifts are entered.

 

[Peter_SSs]

Could you put this in row 55? For column E it would be

=IF(AGGREGATE(14,6,COUNTIF(E20:E32,E20:E32)/((E20:E32<>"OFF")*(E20:E32<>"VAC")),1)=1,"OK","Duplicates")

 

[steve89]

Thats truly amazing it works but can it name the shift that duplicated using whats provided below on the other sheet? similar to how the one formula says missing whatever shift is missing

Excel Workbook
	A
2	AM COOK
3	PM COOK
4	PR COOK
5	AM 1
6	AM 2
7	AM 3
8	AM 4
9	PM 1
10	PM 2
11	PM 3
12	PM 4
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Employee Data

 

[steve89]

also when i put "N/A" on the schedule it says duplicate as two people arent available those days. Thats why i was think the formula would be best referencing the above sheet instead

 

[steve89]

this is what we have so far you see how the formula has missing and it names the shift cant your do the same for a duplicate by naming the shifts

Excel Workbook
	D
20	OFF
21	AM 1
22	VAC
23	AM 4
24	AM 3
25	N/A
26	AM 2
27	PM 1
28	PM 3
29	PM 2
30	OFF
31	PM 4
32	OFF
53
54
55	NO DUPLICATE SHIFTS
MASTER UNIVERSAL SCHEDULE

 

[Peter_SSs]

Like this?

Excel Workbook
	D	E
20	OFF	AM 1
21	AM 1	N/A
22	VAC	VAC
23	AM 4	AM 3
24	AM 3	OFF
25	N/A	AM 4
26	PM 1	PM 1
27	AM 2	OFF
28	PM 3	N/A
29	OFF	VAC
30	OFF	PM 1
31	PM 4	AM 1
32	PM 2	OFF
33
34
53
54
55	OK	Duplicates: PM 1, AM 1
Duplicate Shifts