I have this table of what people are missing on each month, and what are they missing(maked as missing) or they have(marked with an “X”) or if the guy wasn’t working at that time, it says not working.
This is the table layout
Table Layout
I want to turn that into a list with all the missing months of each thing so it can look maybe like this
Year 2023
Worker1_name
NÒMINA:(Months missing here)
REGISTRE DE JORNADA:(Months missing here)
Year 2023
Worker2_name
...
And so on so forth, I don’t know if this is possible though.
| Month | AGUILERA AVILA, RAFAEL | ANGUITA MARTIN, MARTA | AUMEDES SANCHEZ, JOSEP Mª | GINESTÀ ALVAREZ DE LARA, Mª.DOLORES | GRIMAL RIBOT, LLUIS | HERNANDEZ FUENTES, DOLORES | ILLA FONT, ADRIÀ | LOPEZ GUERRERO, ALEXIS | LOPEZ RUIZ, JAVIER | MARTINEZ RECHE, JOAQUIN | NUÑEZ HIDALGO, LUIS | RUIZ FERNANDO, MIREIA | SANCHEZ RIO-VALLE, MANUEL DIEGO | TAMBADOU, YUSUPHA | VALENZUELA MONTORE, JESUS | VERA VICO, JOSE | VERDUGUEZ ENCINAS, VICTOR RONALD | WAGGEH SAWANEH, MOHAMED KISSIMA |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| JAN NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | Missing | X | X | X |
| JAN REGISTRE DE JORNADA | Missing | X | Missing | Missing | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| FEB NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | Missing | X | X | X |
| FEB REGISTRE DE JORNADA | Missing | X | Missing | Missing | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| MAR NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | Missing | X | X | X |
| MAR REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| APR NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | Missing | X | X | X |
| APR REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| MAY NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | Missing | X | X | X |
| MAY REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| JUN NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | X | X | X | X |
| JUN REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | X | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| JUL NÒMINA | Missing | X | X | X | X | X | X | X | Not working | X | X | X | Not working | X | X | X | X | X |
| JUL REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| AUG NÒMINA | Missing | Missing | Missing | Missing | Missing | X | Missing | Not working | X | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| AUG REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| SEP NÒMINA | Missing | X | X | X | X | Missing | X | Not working | X | X | X | Not working | X | X | X | X | X | |
| SEP REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| OCT NÒMINA | Missing | X | X | X | X | X | X | Not working | X | Missing | X | Not working | X | X | X | X | X | |
| OCT REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| NOV NÒMINA | Missing | X | X | X | X | X | X | Not working | X | X | X | X | Not working | X | X | X | X | X |
| NOV REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
| DEC NÒMINA | Missing | X | X | X | X | X | X | Not working | X | X | X | X | Not working | X | X | X | X | X |
| DEC REGISTRE DE JORNADA | Missing | X | X | X | X | X | Missing | Not working | Missing | Missing | Missing | X | Not working | Missing | Missing | Missing | Missing | Missing |
7
Here’s a quick attempt; I’m not sure how to get the years, so I have used a fixed string. This requires Microsoft 365. You can check the steps by changing result to months, register etc. Please look up the references for LET, LAMBDA etc. from here: LET function – Microsoft Support
Enter the following in the first cell for the results:
=LET(
header, A3:T3,
payroll_data, A4:T26,
comment_1, "Take first row, drop the first column when needed",
employee_names, DROP(header, , 1),
months, TEXTBEFORE(CHOOSECOLS(payroll_data, 1), " "),
register, TEXTAFTER(CHOOSECOLS(payroll_data, 1), " "),
result, REDUCE(
"Missing months for employees",
DROP(employee_names, , 1),
LAMBDA(list, name,
LET(
is_missing, CHOOSECOLS(payroll_data, XMATCH(name, employee_names)) =
"Missing",
missing_months, LAMBDA(name, for_register,
for_register & ": " &
ARRAYTOTEXT(
FILTER(months, (register = for_register) * is_missing, "--")
)
),
VSTACK(
list,
VSTACK(
"Year 2023",
name,
missing_months(name, "NÒMINA"),
missing_months(name, "REGISTRE DE JORNADA"),
""
)
)
)
)
),
result
)