?
Solved

Comparing two Excel lists

Posted on 2012-04-08
5
Medium Priority
?
474 Views
Last Modified: 2012-05-14
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?
0
Comment
Question by:smith1974
  • 2
  • 2
5 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 37821824
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.
0
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 668 total points
ID: 37821853
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
0
 
LVL 42

Assisted Solution

by:dlmille
dlmille earned 668 total points
ID: 37822213
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
0
 
LVL 9

Expert Comment

by:Anthony Mellor
ID: 37834885
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
0
 
LVL 9

Assisted Solution

by:Anthony Mellor
Anthony Mellor earned 664 total points
ID: 37835021
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

As a person who answers a lot of questions, I often see code that could be simplified, made easier to read, and perhaps most importantly made easier to maintain if the code was modified to use the Select Case statement. This article explains how to…
Debits & Credits have been the foundation of financial record keeping since 1494 - over 500 years. Excel is a brilliant tool for leveraging this ancient power - not least with Pivot Tables, sorting and filtering.  This article seeks by illustration …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question