Python Script to compare two Excel Workbooks using Pandas and export the final results in detail
Budget: $10 – $30 USD
I am wanting to have a Python script created to compare two Excel workbooks. This script will need to complete the below tasks:
1st - check for duplicate file numbers on the new sheet - if no duplicates then create new sheet on the final file named DUPLICATES and put 'No Data'
If yes it finds duplicates - copy the duplicates and put on the output file as a ENTIRE ROW on a new tab
named DUPLICATES and REMOVE entire rows of duplicates from the main xls file
2nd - compare the new sheet against the old sheet for differences - and show the detail below;
3rd - if rows on the new file and NOT on the old file - place ENTIRE ROW on tab named NEW
4th - if NOT on the new file but was on the old file - place ENTIRE ROW on a tab named TERM
5th - check for changes using COMPLETE ROWS - if any cells are different on the new file from the old file then move ENTIRE ROW to a tab named CHANGE showing the old value AND new value
6th - export the completed sheet with all data - Evacuation List, Sheet1, NEW, TERM, CHANGE, DUPLICATES
The point of this script is to find New employees, Terminated employees, employees that have changed departments, and employees listed with duplicate file numbers - as well as show the normal tabs which are Evacuation List and Sheet1 which are required by another program to remain in place
This Python script should be able to accurately complete the above tasks and export a file with the results as needed with all 6 tabs showing the information needed. I am attaching two workbooks that you can use to test with. If you have any questions feel free to contact me.
Sheets will be compared on one column for most of the data and will need to be compared by row to find the changes. I already have some of the code that will be required to fix the employee numbers if needed.
1st - check for duplicate file numbers on the new sheet - if no duplicates then create new sheet on the final file named DUPLICATES and put 'No Data'
If yes it finds duplicates - copy the duplicates and put on the output file as a ENTIRE ROW on a new tab
named DUPLICATES and REMOVE entire rows of duplicates from the main xls file
2nd - compare the new sheet against the old sheet for differences - and show the detail below;
3rd - if rows on the new file and NOT on the old file - place ENTIRE ROW on tab named NEW
4th - if NOT on the new file but was on the old file - place ENTIRE ROW on a tab named TERM
5th - check for changes using COMPLETE ROWS - if any cells are different on the new file from the old file then move ENTIRE ROW to a tab named CHANGE showing the old value AND new value
6th - export the completed sheet with all data - Evacuation List, Sheet1, NEW, TERM, CHANGE, DUPLICATES
The point of this script is to find New employees, Terminated employees, employees that have changed departments, and employees listed with duplicate file numbers - as well as show the normal tabs which are Evacuation List and Sheet1 which are required by another program to remain in place
This Python script should be able to accurately complete the above tasks and export a file with the results as needed with all 6 tabs showing the information needed. I am attaching two workbooks that you can use to test with. If you have any questions feel free to contact me.
Sheets will be compared on one column for most of the data and will need to be compared by row to find the changes. I already have some of the code that will be required to fix the employee numbers if needed.