• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 472
  • Last Modified:

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?
0
smith1974
Asked:
smith1974
  • 2
  • 2
3 Solutions
 
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.
0
 
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
0
 
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
0
 
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
0
 
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
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.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now