How to compare and combine information from 2 different excelworksheets (VLOOKUP ? Pivot Table ?)

[Michelle York]

I help manage the student logins in our school. At the beginning of each year, I have to notify the district of which students in our building DON'T have building logins and which students have moved and need deleted from our list.

I have all of the information needed, but the data is received from two different sources and I'm trying to figure out a way to automate the system.

The "SYSOP" database (the one showing current building login information) has the following data pieces:
-Student login
-Student name (Last,First)
-Student ID #

When exported as a CSV file, this gives me 3 columns with the above information. These are the ONLY pieces of information available in this database.

The "PowerSchool" database allows me to create a list of all CURRENT list of students in the building. This is exported from the district's student management software. Among the fields that I can access through this software are the following relevant fields:
-Student last name
-Student first name
-Grade level
-Teacher name
-Student ID

These two data sources DON'T automatically update themselves at the same time with new student information, therefore it's up to me to notify the district of changes that need to be made in the SYSOP database.

I need a way to compare the two excel worksheets and find out which records/rows are NOT in both data sources.
1. which students are listed in the always up-to-date Powerschool list that AREN'T listed in the SYSOP list and need added.
2. which students are listed in the sometimes out-of-date SYSOP list that AREN'T in PowerSchool that I need to have them delete from SYSOP.

The fields that could be used to cross-reference or match the worksheets are the student ID and the students last and first name (although one database shows these together in one field/column and one database shows it as twofields/columns).

If possible (in my dream world) I'd love to create a new worksheet that combines some of the data from each source. I'd love to be able to give each teacher a list that includes all of the information from PowerSchool and the one piece of information that is only found in SYSOP--the login username.

I know that this sounds confusing--it does to me just typing it. I'm comfortable with some aspects of Excel, but haven't done much with VLOOKUP or Pivot Tables. My guess is that I need to use either one or a combination of these, but I'm not sure how. I'm willing to learn how to do it, I'm just not sure which will do what I want in order to compare and find the differences between the two datasources that contain some information that is the SAME and some information that is DIFFERENT.

 

[Salmon Egg]

I help manage the student logins in our school. At the beginning of each
year, I have to notify the district of which students in our building DON'T
have building logins and which students have moved and need deleted from our
list.

I have all of the information needed, but the data is received from two
different sources and I'm trying to figure out a way to automate the system.

The "SYSOP" database (the one showing current building login information) has
the following data pieces:
-Student login
-Student name (Last,First)
-Student ID #

When exported as a CSV file, this gives me 3 columns with the above
information. These are the ONLY pieces of information available in this
database.

The "PowerSchool" database allows me to create a list of all CURRENT list of
students in the building. This is exported from the district's student
management software. Among the fields that I can access through this
software are the following relevant fields:
-Student last name
-Student first name
-Grade level
-Teacher name
-Student ID

These two data sources DON'T automatically update themselves at the same time
with new student information, therefore it's up to me to notify the district
of changes that need to be made in the SYSOP database.

I need a way to compare the two excel worksheets and find out which
records/rows are NOT in both data sources.
1. which students are listed in the always up-to-date Powerschool list that
AREN'T listed in the SYSOP list and need added.
2. which students are listed in the sometimes out-of-date SYSOP list that
AREN'T in PowerSchool that I need to have them delete from SYSOP.

The fields that could be used to cross-reference or match the worksheets are
the student ID and the students last and first name (although one database
shows these together in one field/column and one database shows it as two
fields/columns).

If possible (in my dream world) I'd love to create a new worksheet that
combines some of the data from each source. I'd love to be able to give each
teacher a list that includes all of the information from PowerSchool and the
one piece of information that is only found in SYSOP--the login username.

I know that this sounds confusing--it does to me just typing it. I'm
comfortable with some aspects of Excel, but haven't done much with VLOOKUP or
Pivot Tables. My guess is that I need to use either one or a combination of
these, but I'm not sure how. I'm willing to learn how to do it, I'm just not
sure which will do what I want in order to compare and find the differences
between the two datasources that contain some information that is the SAME
and some information that is DIFFERENT.

GThis was rather complicated so I am only giving you an approach--not
the details.
Start with an expendable worksheet A that is a copy of the more
complicated (more columns) database.

Use another expendable worksheet B of the simpler database. Arrange
columns in B to match the column order and widths of A.

Add dummy (empty) columns to B so that A and B have the same structure.

You can now combine A and B to form C.

Sort on the column contents that will order C so that matching row are
adjoined vertically.

At this point you should be able to test to see if two adjacent rows
refer to the same person.

If you are into macros, you can transfer data from one data base to
another or remove duplicagtes.

--

Sam

Conservatives are against Darwinism but for natural selection.
Liberals are for Darwinism but totally against any selection.