Differences on XL sheets

On the attached XL 2010 workbook, I have names on both the personnel and HR sheets. Many of them are the same but there are some on one sheet that are not on the other. How can I find out which names are on the personnel sheet that are not on the HR sheet?

Thanks,
C--Users-RLaughridge-Desktop-Temp-Differ
contrainAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Please look in columns [D] & [E] of both the
& [Personnel] worksheets in the attached workbook.

Given my guess at the extension of your posted workbook (as ".xls"), I did not presume you were using MS-Excel 2007 onwards, so the in-cell formula I used in column [E] (if both worksheets) is compatible with MS-Excel 2003 to 2013.  If you are using MS-Excel 2007 onwards, it could be simplified, if required.
Q-28698116.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
contrainAuthor Commented:
Hi fanpages. I am using XL 2010 but it seems to work there. You mentioned it could be simplified using xl 2007 onward, what would that be? Your first solution works, but if there is a simplier one, that may be better. Thanks,
0
Saurabh Singh TeotiaCommented:
Contrain,

You can simply do this...

In Personal sheet use this formula..

=IF(SUMPRODUCT((HR!Last_Name=Personnel!B4)*(HR!First_Name=Personnel!C4))>0,"Yes","No")

Open in new window


Then in HR Sheet use this formula...

=IF(SUMPRODUCT((PLast_Name=HR!B4)*(PFirst_Name=HR!C4))>0,"Yes","No")

Open in new window


Note:- I changed your name range of personal sheet since for both sheets you had the same name range..

Enclosed is your workbook for your reference...

Saurabh...
C--UsersTemp.xls
0
[ fanpages ]IT Services ConsultantCommented:
"Hi fanpages. I am using XL 2010 but it seems to work there. You mentioned it could be simplified using xl 2007 onward, what would that be? Your first solution works, but if there is a simplier one, that may be better. Thanks,"

Hi contrain,

It's not 'better', as such, as that can be subjective; it is just a different approach.  The outcome is the same.  Saurabh has taken a different route to address your requirements, so please just use the method you prefer.


Saurabh:
"Note:- I changed your name range of personal sheet since for both sheets you had the same name range.."

I assumed that was intentional & possibly used as part of the wider project.  Note the named ranges that also exist in both worksheets relating to columns that, I presume, have been removed as they contained sensitive information (not relevant to this question).
0
contrainAuthor Commented:
This gave me the answer I was looking for.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.