Help Please: Comparison in Excel

  • Thread starter Thread starter Fitzwilliam Darcy
  • Start date Start date
F

Fitzwilliam Darcy

We have an Excel spreadsheet with a list of students signed up for a
seminar for spring quarter. We also have a long list of students
signed up for summer quarter.

We want to generate a new list using these two spreadsheets with ONLY
all spring quarter students that are not signed up for summer so that
we can remind them to enroll.

Is there an easy way to do this?

Summary:

NEW LIST should be students on the spring list, but not on the summer
list.

NOT ON NEW LIST if they are on the spring list and on the summer list.
NOT ON NEW LIST if they are not on the spring list and they are on the
summer list.
 
Do you have only names of students or have their ID numbers as well?
The comparison would be much straight forward and easy if you have
their IDs included in the file.
The following isbased on Student IDs in Columns A and B, while the
Formula is in Column C.

Summer Spring
3 1 =VLOOKUP(B2,$A$2:$A$6,1,FALSE)
2 2 =VLOOKUP(B3,$A$2:$A$6,1,FALSE)
9 3 =VLOOKUP(B4,$A$2:$A$6,1,FALSE)
7 5 =VLOOKUP(B5,$A$2:$A$6,1,FALSE)
11 6 =VLOOKUP(B6,$A$2:$A$6,1,FALSE)
7 =VLOOKUP(B7,$A$2:$A$6,1,FALSE)
9 =VLOOKUP(B8,$A$2:$A$6,1,FALSE)
10 =VLOOKUP(B9,$A$2:$A$6,1,FALSE)
11 =VLOOKUP(B10,$A$2:$A$6,1,FALSE)

The result will look like this

Summer Spring
3 1 #N/A
2 2 2
9 3 3
7 5 #N/A
11 6 #N/A
7 7
9 9
10 #N/A
11 11

#N/A means a Spring student has not signed on Summer, while student
number means this student is on Summer List. Filter your column C for
#N/A.

Aqib Rizvi
 
Do you have only names of students or have their ID numbers as well?
The comparison would be much straight forward and easy if you have
their IDs included in the file.
The following isbased on Student IDs in Columns A and B, while the
Formula is in Column C.

Summer  Spring
3       1       =VLOOKUP(B2,$A$2:$A$6,1,FALSE)
2       2       =VLOOKUP(B3,$A$2:$A$6,1,FALSE)
9       3       =VLOOKUP(B4,$A$2:$A$6,1,FALSE)
7       5       =VLOOKUP(B5,$A$2:$A$6,1,FALSE)
11      6       =VLOOKUP(B6,$A$2:$A$6,1,FALSE)
        7       =VLOOKUP(B7,$A$2:$A$6,1,FALSE)
        9       =VLOOKUP(B8,$A$2:$A$6,1,FALSE)
        10      =VLOOKUP(B9,$A$2:$A$6,1,FALSE)
        11      =VLOOKUP(B10,$A$2:$A$6,1,FALSE)

The result will look like this

Summer  Spring
3       1       #N/A
2       2       2
9       3       3
7       5       #N/A
11      6       #N/A
        7       7
        9       9
        10      #N/A
        11      11

#N/A means a Spring student has not signed on Summer, while student
number means this student is on Summer List.  Filter your column C for
#N/A.

Aqib Rizvi
Click to expand...

Thank you for the reply. I am still trying to see if what you
suggested will work for us.

Our spreadsheets do have student IDs. Each row in the spring
spreadsheet consists of the following: Student ID, Last Name, First
Name, Address1, City, State, Zip, Email, Phone, etc.

The student ID format is like B1234567890 and there are a lot of
students in each file.

What would your comparison do for a student that is enrolled summer
but not spring?

Example:
Spring BOB
Summer BOB
;Enrolled both, Bob should not be on the list

Spring TED
Summer
;TED is enrolled spring, but not summer, so TED should be on the list

Spring
Summer SAM
;SAM is enrolled summer but was not enrolled spring, so SAM should not
be on the list

So the new list we are hoping for, from the above example, would
consist only of TED. Ted was here spring, but is not here summer so we
want to invite him back for summer.

Any further advice? Thanks again.
 
Back
Top