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

hi i havent heard back and still need help with this

 

[Peter_SSs]

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

hi i havent heard back and still need help with this

Did you re-cap the thread and note post #14 ? If we could see a small section of actual (or dummy) data, including the expected results and exact layout & get a description relating to that sample data, the problem would most likely have been resolved 18 months ago.

 

[steve89]

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

Hi thank you for your reply yes we already went over this two years ago and the results were what I was looking for as much as I appreciated it. The thread has the layout and expected results already displayed just no one is able to crack it I guess haha it’s a difficult one as this specific formula can’t have two multiples or error with different shifts.

 

[oldbrewer]

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

please start a brand new thread - I have no recollection after 8 months....

 

[Peter_SSs]

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

The thread has the layout and expected results already displayed ....

If you think so that's fine, I'll just move on to one where I think so.
I'm pretty sure your problem would be "crackable" if I knew exactly what you had, where you had it, what you wanted & where you wanted it but as it stands we'll never know.

 

[oldbrewer]

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

I love a challenge ! Put up some simplified data and show us the expected outcome. Like Peter, I believe all excel logic problems are crackable. Often, the hardest part of the problem is lucidly explaining the nature of the problem......

 

[steve89]

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

Sheet1

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD]#Shifts[/TD]
[TD]Cooks[/TD]
[TD][/TD]
[TD]Other[/TD]
[TD]#Shifts[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD]Lenny[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD]George[/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD]Les[/TD]
[TD][/TD]
[TD]Marty[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD]Sam[/TD]
[TD][/TD]
[TD]Mike[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD]Sebough[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Terry[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Spreadsheet Formulas[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]K2[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$L2&"*")[/TD]
[/TR]
[TR]
[TD]O2[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N2&"*")[/TD]
[/TR]
[TR]
[TD]K3[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$L3&"*")[/TD]
[/TR]
[TR]
[TD]O3[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N3&"*")[/TD]
[/TR]
[TR]
[TD]K4[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$L4&"*")[/TD]
[/TR]
[TR]
[TD]O4[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N4&"*")[/TD]
[/TR]
[TR]
[TD]K5[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$L5&"*")[/TD]
[/TR]
[TR]
[TD]O5[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N5&"*")[/TD]
[/TR]
[TR]
[TD]K6[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$L6&"*")[/TD]
[/TR]
[TR]
[TD]O6[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N6&"*")[/TD]
[/TR]
[TR]
[TD]O7[/TD]
[TD]=COUNTIF($B$2:$I$8,"*"&$N7&"*")[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]



Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Then in column A I put the Days (1, 2, 3, etc), or Sunday to Saturday for the entire week or month.
Row 1 is the header row where I put the positions for the cooks and others…see the screenshot below.

Sheet1

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD]Day[/TD]
[TD]AM Cook[/TD]
[TD]PM Cook[/TD]
[TD]AM1[/TD]
[TD]AM2[/TD]
[TD]AM3[/TD]
[TD]PM4[/TD]
[TD]PM5[/TD]
[TD]PM6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Bob[/TD]
[TD]George[/TD]
[TD]Terry[/TD]
[TD]Lenny[/TD]
[TD]Mark[/TD]
[TD]Mike[/TD]
[TD]Sebough[/TD]
[TD]Marty[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Missing[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD]Les[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD]Lenny[/TD]
[TD]Terry[/TD]
[TD]Mark[/TD]
[TD]Sebough[/TD]
[TD][/TD]
[TD]Missing[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Missing[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD]Sam[/TD]
[TD]Bob[/TD]
[TD]Terry[/TD]
[TD]Lenny[/TD]
[TD]Mark[/TD]
[TD]Mike[/TD]
[TD]Marty[/TD]
[TD]Sebough[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Missing[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Missing[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Spreadsheet Formulas[/TD]
[/TR]
[TR]
[TD][TABLE="class: cms_table"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]J2[/TD]
[TD]=IF(COUNTA(B2:I2)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]=A2+1[/TD]
[/TR]
[TR]
[TD]J3[/TD]
[TD]=IF(COUNTA(B3:I3)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A4[/TD]
[TD]=A3+1[/TD]
[/TR]
[TR]
[TD]J4[/TD]
[TD]=IF(COUNTA(B4:I4)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]=A4+1[/TD]
[/TR]
[TR]
[TD]J5[/TD]
[TD]=IF(COUNTA(B5:I5)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A6[/TD]
[TD]=A5+1[/TD]
[/TR]
[TR]
[TD]J6[/TD]
[TD]=IF(COUNTA(B6:I6)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]=A6+1[/TD]
[/TR]
[TR]
[TD]J7[/TD]
[TD]=IF(COUNTA(B7:I7)<>8,"Missing","")[/TD]
[/TR]
[TR]
[TD]A8[/TD]
[TD]=A7+1[/TD]
[/TR]
[TR]
[TD]J8[/TD]
[TD]=IF(COUNTA(B8:I8)<>8,"Missing","")[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

this works but still not what were looking for. looking to have A SINGLE CELL say "missing am1 & missing am2" we need to make sure am1-am3 & pm1-pm6 are always scheduled.

 

[Peter_SSs]

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

Thank you, that is much clearer. Here are 3 options for you to consider. I have placed them in columns Q:S but you can use column J once you have decided which one suits you best. I have also hidden some columns to make my screen shot a bit smaller.

Each formula is copied down.
The formula in Q2 requires Excel 2016 through Office 365 and is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
The formula in R2 should work in all versions but is obviously much longer, especially if you have a lot of shifts.
The formula in S2 is a user-defined function. To implement it ..

1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Missing(rWorkers As Range, rShifts As Range) As String
  Dim i As Long
  Dim bMissing As Boolean
  
  For i = 1 To rWorkers.Cells.Count
    If Len(rWorkers.Cells(i).Value) = 0 Then Missing = Missing & ", " & rShifts.Cells(i).Value
  Next i
  If Len(Missing) > 0 Then Missing = "Missing:" & Mid(Missing, 2)
End Function

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	Q	R	S
1	Day	AM Cook	PM Cook	AM1	AM2	AM3	PM4	PM5	PM6		Excel 2016	Earlier	UDF
2	1	Bob	George	Terry	Lenny	Mark	Mike	Sebough	Marty
3	2										Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
4	3	Les	Steve		Lenny	Terry	Mark	Sebough			Missing: AM1, PM6	Missing: AM1, PM6	Missing: AM1, PM6
5	4										Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
6	5	Sam	Bob	Terry	Lenny	Mark	Mike	Marty	Sebough
7	6										Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
8	7										Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6	Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
Missing Shifts

 

[steve89]

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

YOU ARE A GOD OMG thank you so much it works perfectly how in the world do you know how to do that thank you

 

[steve89]

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

formula which you have for Q2 isnt working its displaying the whole formula not actually working also the page randomly stopped accepting the formulas im trying to figure it out now