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
| | 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 |
<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>
</tbody>
| Spreadsheet Formulas |
| Cell | Formula | | 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
