Return a TEXT value if certain TEXT is missing

steve89

Board Regular
Joined
Oct 4, 2015
Messages
152
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
 
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
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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.
 
Upvote 0
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.
 
Upvote 0
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....
 
Upvote 0
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.
 
Upvote 0
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......
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

Sheet1

KLMNO
1#ShiftsCooksOther#Shifts
22BobLenny3
31GeorgeMark3
41LesMarty2
51SamMike2
61SteveSebough3
7Terry3
8

<tbody>
</tbody>


Spreadsheet Formulas
CellFormula
K2=COUNTIF($B$2:$I$8,"*"&$L2&"*")
O2=COUNTIF($B$2:$I$8,"*"&$N2&"*")
K3=COUNTIF($B$2:$I$8,"*"&$L3&"*")
O3=COUNTIF($B$2:$I$8,"*"&$N3&"*")
K4=COUNTIF($B$2:$I$8,"*"&$L4&"*")
O4=COUNTIF($B$2:$I$8,"*"&$N4&"*")
K5=COUNTIF($B$2:$I$8,"*"&$L5&"*")
O5=COUNTIF($B$2:$I$8,"*"&$N5&"*")
K6=COUNTIF($B$2:$I$8,"*"&$L6&"*")
O6=COUNTIF($B$2:$I$8,"*"&$N6&"*")
O7=COUNTIF($B$2:$I$8,"*"&$N7&"*")

<tbody>
</tbody>


<tbody>
</tbody>



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

ABCDEFGHIJ
1DayAM CookPM CookAM1AM2AM3PM4PM5PM6
21BobGeorgeTerryLennyMarkMikeSeboughMarty
32Missing
43LesSteveLennyTerryMarkSeboughMissing
54Missing
65SamBobTerryLennyMarkMikeMartySebough
76Missing
87Missing

<tbody>
</tbody>


Spreadsheet Formulas
CellFormula
J2=IF(COUNTA(B2:I2)<>8,"Missing","")
A3=A2+1
J3=IF(COUNTA(B3:I3)<>8,"Missing","")
A4=A3+1
J4=IF(COUNTA(B4:I4)<>8,"Missing","")
A5=A4+1
J5=IF(COUNTA(B5:I5)<>8,"Missing","")
A6=A5+1
J6=IF(COUNTA(B6:I6)<>8,"Missing","")
A7=A6+1
J7=IF(COUNTA(B7:I7)<>8,"Missing","")
A8=A7+1
J8=IF(COUNTA(B8:I8)<>8,"Missing","")

<tbody>
</tbody>


<tbody>
</tbody>

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.
 
Upvote 0
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)
Code:
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
ABCDEFGHIJQRS
1DayAM CookPM CookAM1AM2AM3PM4PM5PM6Excel 2016EarlierUDF
21BobGeorgeTerryLennyMarkMikeSeboughMarty   
32Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
43LesSteveLennyTerryMarkSeboughMissing: AM1, PM6Missing: AM1, PM6Missing: AM1, PM6
54Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
65SamBobTerryLennyMarkMikeMartySebough
76Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
87Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6Missing: AM Cook, PM Cook, AM1, AM2, AM3, PM4, PM5, PM6
Missing Shifts
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top