Oct 01 2021 05:02 PM
I have a list of employees who submitted their daily reports. I need to compare this list with the complete employee roster to determine which employees did not submit their daily report. How can I do that?
Oct 01 2021 05:40 PM
Oct 02 2021 04:00 AM
It depends on which version of Excel you are, how your data is organized and in which form you'd like to see the result.
That could be helper COUNTIF() column with filtering on it, could be dynamic arrays formulae, could be conditional formatting, could be Power Query, whatever.
Oct 02 2021 06:59 AM
Oct 02 2021 07:10 AM
Thank you, but still better to have sample file.
If these are two different file you may use Power Query to compare data an return the table with records which don't meet condition.
Oct 02 2021 10:28 PM
Solution@pbexpresss Hi. Assuming Excel 365, this is a way
XMATCH looks for all names among those reported.
ISERROR becomes true if they are not in the list of reported hours
FILTER shows all names that have not reported
=FILTER(G2:G6;ISERROR(XMATCH(G2:G6;A2:A4)))
Change G2:G6 to range in your workbook with all names.
Using the function LET makes it easier to read and test the different parts.
Last line is what the cell outputs and changing "output" to eg haveReport shows that part of the calculation.
I prefer to have the definitions indented.
=LET(allNames;G2:G6;
reportedNames;A2:A4;
haveReported;XMATCH(allNames;reportedNames);
haveNotReported;ISERROR(haveReported);
output;FILTER(allNames;haveNotReported);
output
)
Oct 07 2021 05:03 PM
@bosinander The combination of functions worked perfectly. I created a file that maintains my master employee list. When I retrieve the file summarizing my employees' daily report submissions, the macro I created uses your suggestions to compare the list from one file with the other file. Then it gives me a list of employees who did not work that day at the bottom of the daily report submission report.
Thanks,
Feb 02 2023 11:05 AM - edited Feb 02 2023 11:08 AM
=FILTER(G2:G6,ISERROR(XMATCH(G2:G6,A2:A4)))
thx for the answer, it was usfull for me as well, however if you want to copy &past do it from my post
since in the original answer, there is an error they put an ";" instead " ,"
Feb 02 2023 12:28 PM
Oct 02 2021 10:28 PM
Solution@pbexpresss Hi. Assuming Excel 365, this is a way
XMATCH looks for all names among those reported.
ISERROR becomes true if they are not in the list of reported hours
FILTER shows all names that have not reported
=FILTER(G2:G6;ISERROR(XMATCH(G2:G6;A2:A4)))
Change G2:G6 to range in your workbook with all names.
Using the function LET makes it easier to read and test the different parts.
Last line is what the cell outputs and changing "output" to eg haveReport shows that part of the calculation.
I prefer to have the definitions indented.
=LET(allNames;G2:G6;
reportedNames;A2:A4;
haveReported;XMATCH(allNames;reportedNames);
haveNotReported;ISERROR(haveReported);
output;FILTER(allNames;haveNotReported);
output
)