Forum Discussion
Compare Two Columns
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?
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
)
9 Replies
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.
- Okay. Here are the details. Each day my employees submit a daily report to the cloud. A summary report is run automatically and emailed to me by the software company. I need to create a list at the bottom of the report of names of employees who did not submit a report. I was trying to compare this report with a master file of names. How should I do this?
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
)
