Excel Puzzle: Tracking Change in Hours

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

Fitzwilliam Darcy

We export a spreadsheet with the following information:

StudentID, LastName, FirstName, Enrolled Hours
2700, Smith, Bob, 3
19243, Hancock, John, 5
533, Doe, Jane, 7

Every day or two, we would like to export an update of the same
spreadsheet and see if the hours have changed for the student from the
last time we exported. If Bob Smith changed from 3 hours to 12 hours
we would like to know. If Bob's hours remained the same, we want to
ignore him for this report.

We thought to copy the new spreadsheet into the old and sort on the
StudentID and then compare the new hours to the old hours and see if
they had changed.

Like this:
Old Data
2700, Smith, Bob, 3
19243, Hancock, John, 5
533, Doe, Jane, 7

New Data
2700, Smith, Bob, 6
19243, Hancock, John, 5
533, Doe, Jane, 9

Since the data lines up, we can easily use a new column that returns
true (hours are the same) or false (hours have changed). In this case,
Bob and Jane need to be flagged as having hours changed and John
Hancock has no change and can be ignored.

The problem is that enrollment changes so much. Some students will be
on the first export and not the second, new students are on the second
and not the first export. So we cannot simply sort by StudentID to
line them up and compare the hours. They will not line up.

So the new report we want to compare to the old might have:

234, Roberts, Samantha, 3
67233, Davis, Julie, 4
2700, Smith, Bob, 6
34, Rivers, Mark
533, Doe, Jane, 7
43233 Cliff, Dave, 3

Notice there are two new students before Bob Smith and Jane Doe and
John Hancock has dropped out of the new export altogether.

Any suggestions?
 
In a column next to the new list, you could add a VLOOKUP formula based on
the - unique? - student ID:

=VLOOKUP(A2,'[Other workbook.xls]Time Log'!$A:$C,3,FALSE)

and copy that down. Then you could compare that value to the previous one,
and make your decision

=IF(D2=C2,"Ignore","Changed")

or you could combine them into a single formula

=IF(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)=C2,"Ignore","Changed")

You would then sort or filter on this column, and extract your data of
interest.

HTH,
Bernie
MS Excel MVP
 
One other thing: the formula on rows where students have been added since
the last report will return errors on the VLOOKUP, so the formula to handle
that should be

=IF(ISERROR(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)),"New",IF(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)=C2,"Ignore","Changed"))

--

HTH,
Bernie
MS Excel MVP


Bernie Deitrick said:
In a column next to the new list, you could add a VLOOKUP formula based on
the - unique? - student ID:

=VLOOKUP(A2,'[Other workbook.xls]Time Log'!$A:$C,3,FALSE)

and copy that down. Then you could compare that value to the previous
one, and make your decision

=IF(D2=C2,"Ignore","Changed")

or you could combine them into a single formula

=IF(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)=C2,"Ignore","Changed")

You would then sort or filter on this column, and extract your data of
interest.

HTH,
Bernie
MS Excel MVP


Fitzwilliam Darcy said:
We export a spreadsheet with the following information:

StudentID, LastName, FirstName, Enrolled Hours
2700, Smith, Bob, 3
19243, Hancock, John, 5
533, Doe, Jane, 7

Every day or two, we would like to export an update of the same
spreadsheet and see if the hours have changed for the student from the
last time we exported. If Bob Smith changed from 3 hours to 12 hours
we would like to know. If Bob's hours remained the same, we want to
ignore him for this report.

We thought to copy the new spreadsheet into the old and sort on the
StudentID and then compare the new hours to the old hours and see if
they had changed.

Like this:
Old Data
2700, Smith, Bob, 3
19243, Hancock, John, 5
533, Doe, Jane, 7

New Data
2700, Smith, Bob, 6
19243, Hancock, John, 5
533, Doe, Jane, 9

Since the data lines up, we can easily use a new column that returns
true (hours are the same) or false (hours have changed). In this case,
Bob and Jane need to be flagged as having hours changed and John
Hancock has no change and can be ignored.

The problem is that enrollment changes so much. Some students will be
on the first export and not the second, new students are on the second
and not the first export. So we cannot simply sort by StudentID to
line them up and compare the hours. They will not line up.

So the new report we want to compare to the old might have:

234, Roberts, Samantha, 3
67233, Davis, Julie, 4
2700, Smith, Bob, 6
34, Rivers, Mark
533, Doe, Jane, 7
43233 Cliff, Dave, 3

Notice there are two new students before Bob Smith and Jane Doe and
John Hancock has dropped out of the new export altogether.

Any suggestions?
Click to expand...
Click to expand...
 
Hello,

I suggest to load all Student ID's and corresponding hours of the
previous report into a scripting dictionary, then you can easily scan
the current report and compare against current hours. If they are
different (new students' hours would be zero for the old report)
include them into your output.

An intro:
http://technet.microsoft.com/en-us/library/ee176993.aspx

Regards,
Bernd
 
or you could combine them into a single formula

=IF(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)=C2,"Ignore","Changed")
Click to expand...

StudentID is unique.

This looks good, but it is not quite working (probably user error on
my part). Both spreadsheets have 4 columns: A (StudentID), B
(LastName), C (FirstName), D (Enrolled Hours).

The previous spreadsheet is Old.xls and the current one is New.xls. We
want to know if the hours have changed in column D.

Would you please take a look at the vlookup you posted using the
columns I listed above and tell me what should be changed. Thank you
so much for the help!
 
In New.xls, use

=IF(VLOOKUP(A2,'[Old.xls]Time Log'!$A:$D,4,FALSE)=D2,"Ignore","Changed")

Change Time Log to the name of your actual sheet, which you don't mention.

HTH,
Bernie
MS Excel MVP


or you could combine them into a single formula

=IF(VLOOKUP(A2,'[Other workbook.xls]Time
Log'!$A:$C,3,FALSE)=C2,"Ignore","Changed")
Click to expand...

StudentID is unique.

This looks good, but it is not quite working (probably user error on
my part). Both spreadsheets have 4 columns: A (StudentID), B
(LastName), C (FirstName), D (Enrolled Hours).

The previous spreadsheet is Old.xls and the current one is New.xls. We
want to know if the hours have changed in column D.

Would you please take a look at the vlookup you posted using the
columns I listed above and tell me what should be changed. Thank you
so much for the help!
 
In New.xls, use

=IF(VLOOKUP(A2,'[Old.xls]Time Log'!$A:$D,4,FALSE)=D2,"Ignore","Changed")

Change Time Log to the name of your actual sheet, which you don't mention..

HTH,
Bernie
MS Excel MVP
Click to expand...

Thank you so much for your help. This works perfectly.
 
Back
Top