Solved

Comparing two Excel lists

Posted on 2012-04-08
5
466 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 167 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 167 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 166 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

726 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