Need to create a list of non-duplicates

J

JRobertson

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

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

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

Jim Cone

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" <[email protected]>
wrote in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top