I have two spreadsheets that are updated monthly. The first sheet contains a list of authorized employees (name, ID number, dates of employment). The second is an extract from security badge readers, containing names, badge IDs, and door numbers for all transactions. I need to compare the names from the badge transactions to the list of authorized employees and generate a separate listing of exceptions. Those would be contractors or service personnel that need to be authorized for each individual visit.

How can I automate this process and have the non-employee names show up in a table or separate column?

Additionally, is there an easy way to copy the transactions for all non-employees into a separate sheet that I can forward to security for validation against front desk sign-in and maintenance records?

Assuming your authorized employee list is on Sheet2 in column B and
your badge extract list is in Column D on another sheet, then...

In a column next to the badge extract list use a formula similar to...
=ISNUMBER(MATCH(D1,Sheet2!B1:B48,0))
(it must be filled down to the bottom of the list)

The formula returns True if the name is found in the authorized list, otherwise False.
Sort by the true/false column, copy the False items and paste on another sheet.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
editorial review of special sort excel add-in (30 ways to sort)





"JRobertson"
wrote in message
...

I have two spreadsheets that are updated monthly. The first sheet
contains a list of authorized employees (name, ID number, dates of
employment). The second is an extract from security badge readers,
containing names, badge IDs, and door numbers for all transactions. I
need to compare the names from the badge transactions to the list of
authorized employees and generate a separate listing of exceptions.
Those would be contractors or service personnel that need to be
authorized for each individual visit.

How can I automate this process and have the non-employee names show up
in a table or separate column?

Additionally, is there an easy way to copy the transactions for all
non-employees into a separate sheet that I can forward to security for
validation against front desk sign-in and maintenance records?




--
JRobertson