?
Solved

Comparing two Excel lists

Posted on 2012-04-08
5
Medium Priority
?
468 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

764 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