So I have a dataset that is updated once a month from a different department (I am not pulling the data, so I kind of have to take what I can get!)
Each month, they run a report that dumps all records. Most of the records from one month to another month aren't touched, so no change. But there is no marker that tells me if there is a change. So I have to compare the "new" dump with the "old" dump and then delete records where no change has taken place. But the kicker is that in my database, I have added info to all records (shown in the Green Columns, G, H, I -- (This is for some class offerings at a school.)
BTW, I'm on a MAC, and for some reason I have problems getting the XL2BB addin to work... Sorry... know that would be easier!
So an example:
GREEN COLUMNS... This is data I have added and that does not exist in the dumps I receive.
BLUE: I receive an entire dump each time. So when I append files, I have tons of records that are duplicated.
BLUE STRIKE THROUGH ... these are the duplicate records that have had no change... they just need to be deleted.
BLUE RECORDS NO STRIKE THROUGH ... these are the "almost" duplicate records... but note the time changes in red. So what needs to happen is my green columns need to be copied to the "almost" duplicate record, and then the old record deleted.
Hope this makes sense.
My approach is that I think I need to add a column in the dump that has "dump date" in it. Then when I append, I can tell what is an old versus new record.
I also need to add a FLAG to my Master File which is a Delete Indicator (just tells me that the record needs to be deleted)
Then I can compare the Class Date, Start Time, End Time, Class Name and see if there are any changes... if there are no changes, I can turn on the delete flag. If there are changes, I need to copy the Green Columns to the new record and then turn on the delete flag for the old record. But doing this is another story!!! I'm not sure how to set up my macro, how to do the compare (assume with a concate?) and then how to set flags and delete...
Again, sorry for the long post and the confusions... I'm betting that I have explained this so great, so probably questions...
Col A. Col B Col C Col D Col E Col F Col G Col H. Col I
Class # Session Date Start End Class Name Instructor. Asst Instr Material Notice
So you can see where the new dump has been added in (i.e. each record is basically duplicated - new records are in blue). Classes 550, 620, 612, 616, 658 were in the OLD dump and are also in the new dump. However, 620 and 616 have been changed... not the start time and end times of these two classes. Also note that 781 and 782 are classes that have been added (they are new records in the new dump). -- don't have to worry about these.
So I need to delete the blue records for 550, 612, 658 (ones with the strike through).
For the two classes that have had their times changed (620 and 616), I need to copy the old green data to the new record and then delete the old record.
Thanks for any help with this confusing thing! It would be much easier if they could just supply me with records that have changed!!!! But getting them to do that is more difficult then doing this believe it or not!
Each month, they run a report that dumps all records. Most of the records from one month to another month aren't touched, so no change. But there is no marker that tells me if there is a change. So I have to compare the "new" dump with the "old" dump and then delete records where no change has taken place. But the kicker is that in my database, I have added info to all records (shown in the Green Columns, G, H, I -- (This is for some class offerings at a school.)
BTW, I'm on a MAC, and for some reason I have problems getting the XL2BB addin to work... Sorry... know that would be easier!
So an example:
GREEN COLUMNS... This is data I have added and that does not exist in the dumps I receive.
BLUE: I receive an entire dump each time. So when I append files, I have tons of records that are duplicated.
BLUE STRIKE THROUGH ... these are the duplicate records that have had no change... they just need to be deleted.
BLUE RECORDS NO STRIKE THROUGH ... these are the "almost" duplicate records... but note the time changes in red. So what needs to happen is my green columns need to be copied to the "almost" duplicate record, and then the old record deleted.
Hope this makes sense.
My approach is that I think I need to add a column in the dump that has "dump date" in it. Then when I append, I can tell what is an old versus new record.
I also need to add a FLAG to my Master File which is a Delete Indicator (just tells me that the record needs to be deleted)
Then I can compare the Class Date, Start Time, End Time, Class Name and see if there are any changes... if there are no changes, I can turn on the delete flag. If there are changes, I need to copy the Green Columns to the new record and then turn on the delete flag for the old record. But doing this is another story!!! I'm not sure how to set up my macro, how to do the compare (assume with a concate?) and then how to set flags and delete...
Again, sorry for the long post and the confusions... I'm betting that I have explained this so great, so probably questions...
Col A. Col B Col C Col D Col E Col F Col G Col H. Col I
Class # Session Date Start End Class Name Instructor. Asst Instr Material Notice
550 | Thursday, July 30, 2020 | 6:00 PM | 8:00 PM | Leading Projects through Execution | Shane | Tom | Ordered | |
550 | 6:00 PM | 8:00 PM | Leading Projects through Execution | Shane | ||||
620 | Thursday, July 30, 2020 | 6:30 PM | 9:30 PM | HR Law | Kelley | Jack | ||
620 | Thursday, July 30, 2020 | 6:30 PM | 9:00 PM | HR Law | Kelley | |||
Friday, July 31, 2020 | ||||||||
612 | Saturday, August 1, 2020 | 9:00 AM | 1:00 PM | Lean Six Sigma Green Belt Certification | Ian | Ann | ||
612 | 9:00 AM | 1:00 PM | Lean Six Sigma Green Belt Certification | Ian | ||||
Sunday, August 2, 2020 | ||||||||
Monday, August 3, 2020 | ||||||||
781 | Monday, August 3, 2020 | 12:00 PM | 6:00 PM | (Amazon) Continuous Improvement Champion | Shane | Tom | Ordered | |
782 | Tuesday, August 4, 2020 | 9:00 AM | 5:00 PM | (Amazon) Lean Six Sigma Yellow Belt Certification | Shane | |||
616 | Tuesday, August 4, 2020 | 6:30 PM | 9:30 PM | Employee Relations and Equal Employment Opportunity | Linda | Ann | Ordered | |
616 | Tuesday, August 4, 2020 | 7:30 PM | 9:30 PM | Employee Relations and Equal Employment Opportunity | Linda | |||
658 | Wednesday, August 5, 2020 | 1:00 PM | 3:00 PM | (OneBlood) Continuous Improvement Fundamentals | Shane | Tom | ||
658 | 1:00 PM | 3:00 PM | (OneBlood) Continuous Improvement Fundamentals | Shane |
So you can see where the new dump has been added in (i.e. each record is basically duplicated - new records are in blue). Classes 550, 620, 612, 616, 658 were in the OLD dump and are also in the new dump. However, 620 and 616 have been changed... not the start time and end times of these two classes. Also note that 781 and 782 are classes that have been added (they are new records in the new dump). -- don't have to worry about these.
So I need to delete the blue records for 550, 612, 658 (ones with the strike through).
For the two classes that have had their times changed (620 and 616), I need to copy the old green data to the new record and then delete the old record.
Thanks for any help with this confusing thing! It would be much easier if they could just supply me with records that have changed!!!! But getting them to do that is more difficult then doing this believe it or not!
