Return a TEXT value if certain TEXT is missing

S

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
 
Sort by date Sort by votes
Re: Need help..Want to return a TEXT value if certain TEXT is missing

amazing thank you its literally perfect thank you so much
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Re: Need help..Want to return a TEXT value if certain TEXT is missing

hi any way i can send you en excel sheet last piece of it is ive always used one column and row to do a count if formula so for example =+COUNTIF(B4:O4,"am 1")*8 ----- but i would like to have a control sheet so any account manager can enter there own positions and have its own value so in this case it would be using data from two sheets. I use b4:04 to look for anything that equals "AM 1" but i want to use another sheet instead and needs to be universal hard to explain. As you see below this is where the manager will enter his/her facility position names as they vary per facility and there specific hours as that varies as well. I would like the schedule to grab any information pulled from that sheet and be a countif formula.

[TABLE="width: 4018"]
<tbody>[TR]
[TD="class: xl66, width: 526"]ERIC DIXOn[/TD]
[TD="class: xl67, width: 252"]OFF[/TD]
[TD="class: xl67, width: 222"]AM 1[/TD]
[TD="class: xl67, width: 243"]3[/TD]
[TD="class: xl67, width: 251"]OFF[/TD]
[TD="class: xl67, width: 222"]3[/TD]
[TD="class: xl67, width: 222"]3[/TD]
[TD="class: xl67, width: 266"]2[/TD]
[TD="class: xl67, width: 222"]2[/TD]
[TD="class: xl67, width: 222"]OFF[/TD]
[TD="class: xl67, width: 222"]3[/TD]
[TD="class: xl67, width: 222"]OFF[/TD]
[TD="class: xl70, width: 222"]3[/TD]
[TD="class: xl70, width: 222"]3[/TD]
[TD="class: xl67, width: 222"]OFF[/TD]
[TD="class: xl69, width: 260"]0[/TD]
[/TR]
</tbody>[/TABLE]


A B C D
[TABLE="width: 716"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]DIETARY SHIFTS/VHS[/TD]
[TD]POSITIONS[/TD]
[TD]POSITION NAME[/TD]
[TD]HOURS FOR SHIFT[/TD]
[/TR]
[TR]
[TD]AM 1[/TD]
[TD]F.S.D[/TD]
[TD]AM 1[/TD]
[TD]8 [/TD]
[/TR]
[TR]
[TD]AM 2[/TD]
[TD]A.F.S.D[/TD]
[TD]AM 2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]AM 3[/TD]
[TD]MANAGER[/TD]
[TD]AM 3[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

never mind i figured it out thanks
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

hi Peter i think the last question i have is an issue im running into.

because every facility has different quantities for shifts its at the sole discretion of the account manager to enter his/her shifts. With the formula you provided its AMAZING but if i use a drop down list and lets say for one facility the options are am1,am2,am3 and am4 but because this is a universal schedule i need to leave empty spaces. Now with the empty spaces the formula is showing this (see below) but of course i use a apostrophe to mimic a character so it doesn't display all those zeros. Additionally because I'm using a drop down list if i write anything in that space is accepts even though its only a specified list but the apostrophes are there which i guess is causing drop down list issues. [TABLE="width: 122"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Missing: 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

hi just following up to see if you have any ideas and also is there a way to incorporate the same formula but for it to say if we scheduled a duplicate shift like am cook twice or something -- thank you
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

hello question can we devise a formula if there are also more then 1 of any of these shifts below scheduled below. the missing text works perfectly and display "missing am1" but is there one i can add to this formula if it has too many "am 1" or any of a duplicate shifts for that day but while still keeping the missing text formula?

Peter_SSs said:
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

Missing Shifts

ABCDEFGHIJQRS
Excel 2016EarlierUDF
Missing: 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: AM1, PM6Missing: AM1, PM6Missing: AM1, PM6
Missing: 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: 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: 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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:42px;"><col style="width:73px;"><col style="width:73px;"><col style="width:48px;"><col style="width:54px;"><col style="width:48px;"><col style="width:48px;"><col style="width:70px;"><col style="width:70px;"><col style="width:24px;"><col style="width:239px;"><col style="width:239px;"><col style="width:239px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]Day[/TD]
[TD="align: center"]AM Cook[/TD]
[TD="align: center"]PM Cook[/TD]
[TD="align: center"]AM1[/TD]
[TD="align: center"]AM2[/TD]
[TD="align: center"]AM3[/TD]
[TD="align: center"]PM4[/TD]
[TD="align: center"]PM5[/TD]
[TD="align: center"]PM6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]George[/TD]
[TD="align: center"]Terry[/TD]
[TD="align: center"]Lenny[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]Sebough[/TD]
[TD="align: center"]Marty[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Les[/TD]
[TD="align: center"]Steve[/TD]

[TD="align: center"]Lenny[/TD]
[TD="align: center"]Terry[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Sebough[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Sam[/TD]
[TD="align: center"]Bob[/TD]
[TD="align: center"]Terry[/TD]
[TD="align: center"]Lenny[/TD]
[TD="align: center"]Mark[/TD]
[TD="align: center"]Mike[/TD]
[TD="align: center"]Marty[/TD]
[TD="align: center"]Sebough[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]7[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
Q2{=IF(COUNTA(B2:I2)=COUNTA(B$1:I$1),"","Missing: "&TEXTJOIN(", ",,IF(B2:I2="",B$1:I$1,"")))}
R2=IF(COUNTA(B2:I2)=COUNTA(B$1:I$1),"",SUBSTITUTE("Missing: "&IF(B2="",", "&B$1,"")&IF(C2="",", "&C$1,"")&IF(D2="",", "&D$1,"")&IF(E2="",", "&E$1,"")&IF(F2="",", "&F$1,"")&IF(G2="",", "&G$1,"")&IF(H2="",", "&H$1,"")&IF(I2="",", "&I$1,""),", ","",1))
S2=Missing(B2:I2,B$1:I$1)

<tbody>
</tbody>
Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>



Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

=IF(TEXTJOIN(", ",,IF(COUNTIF(G10:G52,'Employee Data'!$A$2:$A$29),"",'Employee Data'!$A$2:$A$29))="","","Missing: "&TEXTJOIN(", ",,IF(COUNTIF(G10:G52,'Employee Data'!$A$2:$A$29),"",'Employee Data'!$A$2:$A$29)))

formula works great but if there are empty cells its returns "missing 0" can there be an error if there so it doesnt display that when theres an empty cell?
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

Difficult to tell without some simple sample data and expected output.
 
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

Excel Workbook
CDEFGHIJKLMNOP
20AM 1OFFAM 1STOCKAM 1AM 2OFFOFFAM 1AM 1STOCKOFFVACAM 1
21OFFAM 1AM 2OFFPR COOKPR COOKPM COOKPM COOKPR COOKPR COOKAM 1PM COOKOFFOFF
22OFFVACVACVACOFFVACVACVACVACVACVACVACOFFOFF
23OFFAM 4AM 3AM 2AM 2OFFAM 2AM 2AM 2AM 2AM 4AM 1OFFOFF
24AM 2AM 3OFFAM 3PM 1AM 3OFFOFFAM 3AM 3PM 1OFFAM 4AM 3
25AM 4N/AAM 4PR COOKN/AR/OAM 1N/AN/AAM 4PM 2N/AAM 3AM 4
26OFFPM 1PM 1AM 1AM 3OFFAM 3AM 1PM 1PM 1OFFAM 3PM 2OFF
27AM 3/PM 3AM 2OFFAM 4N/AAM 1AM 4/ PM 4AM 4OFFOFFAM 3AM 4AM 2PM 1
28PM 2PM 3PM 2OFFOFFAM 4PM 2AM 3AM 4PM 4PM 4PM 1OFFAM 2
29AM 3OFFPM 3PM 2PM 2PM 2OFFPM 2PM 3PM 2OFFAM 2AM 1PM 3
30OFFOFFPM 4PM 4PM 3PM 4PM 3PM 3PM 4PM 3OFFPM 3PM 3PM 2
31PM 4PM 4AM 1PM 3PM 4PM 3PM 1PM 4OFFOFFPM 3PM 4PM 4PM 4
32PM 1PM 2OFFPM 1AM 4PM 1OFFPM 1PM 2OFFAM 2PM 2PM 1OFF
53\
54Missing: PR COOK, PM 3 Missing: PM COOK   Missing: PR COOK, AM 4, PM 4Missing: PR COOK     Missing: PR COOK
55
MASTER UNIVERSAL SCHEDULE







 
Last edited:
Upvote 0
Re: Need help..Want to return a TEXT value if certain TEXT is missing

I'm afraid that doesn't clarify the issue for me.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
sumifs using date and time values
Replies
9
Views
368
Asbestos_Jen
Asbestos_Jen
C
  • Solved
Averaging + & - times in Text format
2
Replies
10
Views
598
Alex Blakenburg
A
R
  • Solved
Combind a Mod formula with Mround
Replies
5
Views
118
Fluff
Fluff
P
  • Question Question
Inserting carriage returns into text strings of varying lengths
Replies
1
Views
263
Cubist
Cubist
B
  • Solved
VB for Searching for ~ 20 words in a large table of text
Replies
3
Views
121
BobTheSpreadsheetBuilder
B

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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