Excel Macro to compare two workbooks and create a new one highlighting changes
Budget: $250 – $750 AUD
High Level Summary
We require an excel macro which will compare the values of cells in the rows of one workbook created today (eg: attached "today file") and compare it to the value of the cells in the rows in a workbook created yesterday (attached "yesterday file"). A new version of the today file will be created (attached "file created by Macro") with all the same data but highlighting the cells where the value is different by changing the background of the cell to green and adding a note stating what the value was in "yesterday file".
Also if a new row is identified highlight the row in blue and change the text to white.
A legend also needs to be added to the bottom of the sheet as seen in c39-41 & D40-41 of "file created by Macro.
Operation
User opens the spreadsheet containing the macro and ideally this presents like an app where the user is prompted to specify
i. Location of yesterdays file
ii. Location of todays file
iii. Location of new file.
Each of these prompts should display the normal windows dliague box.
How to compare
The workbooks will be consistent in format in terms of the row where the data rows commence and the number of actual columns, naturally the number of rows and columns may differ for different instance but not for the today vs. yesterday workbooks.
Using todays file
- Rows 1 to 12 are header/title information, this needs to be copied to the new sheet as is.
- Row 13 will tell you how many columns make up the data on the report, this can vary from report to report. This also copied to the new workbook.
- Column C: from row 14 will be used as a key for the comparison of the data. To identify if the data has changed, this key needs to be found on yesterdays file and then compared against the value of each of the columns.
- This continues until there are no further values in column C of todays file.
- If a new key is identified that is not on yesterdays file the entire row needs to be highlighted in blue with white text for the equivalent number of columns so the recipient can identify it as new data.
Obviously on the next day, the new file generated today complete with its colour coding will become the original file (yesterday) used to compare against a newly created "Today" file....this cycle will continue.
Attached are samples of the 3 files
Note I expect an NDA and IP for this work.
We require an excel macro which will compare the values of cells in the rows of one workbook created today (eg: attached "today file") and compare it to the value of the cells in the rows in a workbook created yesterday (attached "yesterday file"). A new version of the today file will be created (attached "file created by Macro") with all the same data but highlighting the cells where the value is different by changing the background of the cell to green and adding a note stating what the value was in "yesterday file".
Also if a new row is identified highlight the row in blue and change the text to white.
A legend also needs to be added to the bottom of the sheet as seen in c39-41 & D40-41 of "file created by Macro.
Operation
User opens the spreadsheet containing the macro and ideally this presents like an app where the user is prompted to specify
i. Location of yesterdays file
ii. Location of todays file
iii. Location of new file.
Each of these prompts should display the normal windows dliague box.
How to compare
The workbooks will be consistent in format in terms of the row where the data rows commence and the number of actual columns, naturally the number of rows and columns may differ for different instance but not for the today vs. yesterday workbooks.
Using todays file
- Rows 1 to 12 are header/title information, this needs to be copied to the new sheet as is.
- Row 13 will tell you how many columns make up the data on the report, this can vary from report to report. This also copied to the new workbook.
- Column C: from row 14 will be used as a key for the comparison of the data. To identify if the data has changed, this key needs to be found on yesterdays file and then compared against the value of each of the columns.
- This continues until there are no further values in column C of todays file.
- If a new key is identified that is not on yesterdays file the entire row needs to be highlighted in blue with white text for the equivalent number of columns so the recipient can identify it as new data.
Obviously on the next day, the new file generated today complete with its colour coding will become the original file (yesterday) used to compare against a newly created "Today" file....this cycle will continue.
Attached are samples of the 3 files
Note I expect an NDA and IP for this work.