Comparing two Excel lists

I have been given an unenviable task which I hope I can automate :-)

I have a list of approximately 50 users on a Sheet named CLASS1.

I then have a list of approx 30-60 users on Sheets named SUBJECT1, SUBJECT2 and SUBJECT3.

Basically, I need to compare the two lists and identify:

i. Users in CLASS1 who are not on SUBJECT1, SUBJECT2, SUBJECT3
ii. Users in SUBJECT1, SUBJECT2, SUBJECT3 who are not in CLASS1.

Hopefully, I was going to copy the two lists side by side in Excel and use a formula or something to highlight (whether by colour, font or whatever) someone that was in one list but not the other.

Is this possible?
smith1974Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Gerwin Jansen, EE MVETopic Advisor Commented:
Hello, you could use the VLOOKUP function to do this for you. How familiar are you with Excel? VLOOKUP basically searches the first column of a range and returns a value from any cell on the same row.
Gerwin Jansen, EE MVETopic Advisor Commented:
Here's a sample where the first sheet contains the 50 users and the other 3 sheets the subjects.

I added the lookups and an extra column that checks for presence. Is this what you seek?
classes-subjects.xlsx

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
dlmilleCommented:
That's a good solution.  However, I would think just using a less intensive COUNTIF which doesn't require error checking (re: VLOOKUP requires a check for ISNA) to be more appropriate.

E.g.,:
Users in Class1 not in Subject1, Subject2, and not in Subject3 - assumes all have user id data in column A.  In Class1, put this formula in B2 and copy down:

As the count = 0, then the user is NOT in the tab we're checking.

[B2]=SUM(COUNTIF(SUBJECT1!$A:$A,$A2)+COUNTIF(SUBJECT2!$A:A,$A2)+COUNTIF(SUBJECT3!$A:A,$A2))=0

And in each of the Subject 1, 2, 3 tabs, put this formula in B2 and copy down:

For users in the tab that are NOT in Class1:

[B2]=COUNTIF(CLASS1!$A:$A,$A2)=0

----------------

To get the same labeling "PRESENT","NOT PRESENT" put this in B2 of Class1 tab and copy down:
[B2]=IF(SUM(COUNTIF(SUBJECT1!$A:$A,$A2)+COUNTIF(SUBJECT2!$A:A,$A2)+COUNTIF(SUBJECT3!$A:A,$A2))=0,"NOT PRESENT","PRESENT")

and this in the Subject 1,2,3 tabs and copy down:

[B2]=IF(COUNTIF(CLASS1!$A:$A,$A2)=0,"NOT PRESENT","PRESENT")

Using gerwinjansen's starting example, see attachments 1 and 2.  1 for TRUE/FALSE, or the second for PRESENT/NOT PRESENT.

Dave
classes-subjects-r1.xls
classes-subjects-r2.xls
Anthony MellorChartered AccountantCommented:
pivot table solution?screen shot pivot table can be sorted in any order you like.


some of your own test data would be ideal....
PIVOTSUBJECTS.xlsx
Anthony MellorChartered AccountantCommented:
Hopefully this one answers the questions as asked. I have taken the liberty with the data of putting it all in one list. If it were a repetitive task I'd use say powerpivot to refresh the data whenever that happens automatically.

Terms of reference:
i. Users in CLASS1 who are not on SUBJECT1, SUBJECT2, SUBJECT3
ii. Users in SUBJECT1, SUBJECT2, SUBJECT3 who are not in CLASS1.

This shows who is in which classes and who in the subjects is not in class 1.

Screen shot of pivot showing who is in subjects and class1 or not.





and now I cannot delete my first answer, sorry about that as it is now superfluous.

Anthony
PIVOTSUBJECTS.xlsx
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.