I ran a macro that basically takes 2 worksheets that contain thousands of rows. Each row is an employee record. The current Macro looks at the Person # (ColJ in "Report" and ColC in "Exceptions") and then once a match is found between the two sheets, it then compares all contents on the matched rows, and if any differences occur, then it flags them in an exception report in a 3rd worksheet, "Exceptions". This is real handy in that it allows me (1 person) to instantly look over an entire company's employee roster from 1 month to the next and then see exactly what changed. However, I need to update the original master worksheet to reflect the updates that the exceptions flagged. For example:
On "Report" - This is a sample sheet that contains basic employee data (ignoring my own fields in ColA thru ColG). This is my live working sheet and I need it updated every month. Although this is a sample, in real live application this sheet will contain thousands of lines.
Now each month I get a Corporate report that I just copy and past it into Sheet2 in raw data format. This will contain raw data that is more up-to-date than my "Report" sheet. There will inevitably be a whole buch of minor and mojor differences, things like: name changes, job title changes, hirngs and firings, etc. I need that Corporate report to be compared to my live report and anywhere there is an excpeption, add the entire row to a 3rd sheet ("Exceptions") and highlight the thing(s) that were different. It ultimately ends up in generating an Exceptions Report that looks as follows:
Now in real application, this may result in hundreds (if not more) lines of minor data changes. Excluding a complete addition (hire) or deletion (fire/quit) which I will flag and remove via the "x" in ColH on "Exceptions", I would like to draft another macro that compares employee numbers ("Report" ColJ and "Exceptions" ColC) and if there is a match, then copy the results from "Exceptions", ColA thru ColG and paste UNFORMATTED values into "Report" ColH thru ColN .
If possible, this would allow me to update my master sheet with the execution of a macro vs spending hours and hours of time going through each update 1 at a time.
Can someone help me with this?
On "Report" - This is a sample sheet that contains basic employee data (ignoring my own fields in ColA thru ColG). This is my live working sheet and I need it updated every month. Although this is a sample, in real live application this sheet will contain thousands of lines.
| Excel 2003 | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
| 1 | Current A Test | Current B Test | Proposed A Test | Proposed B Test | HR | Acounts | Supervisor Confirmation Needed | Last name | First name | PersNo | Name of Manager (OM) | Organizational Unit | Position Description | Job Description | ||
| 2 | -- | -- | -- | -- | -- | -- | -- | Adams | Penny | 6764 | Production | Technician 1 | A Shift | |||
| 3 | -- | -- | -- | -- | -- | -- | -- | Aparte | Isabel | 1429 | Jim Smith | Production | Technician 1 | A Shift | ||
| 4 | x | x | x | x | -- | -- | -- | Asmante | Henry | 6272 | Frank Laruche | Production | Technician 3 | B Shift | ||
| 5 | x | x | x | x | F | -- | -- | Bering | Charles | 9702 | Gerald Assmague | Maintenance | Machinist | Compressors | ||
| 6 | x | x | x | x | -- | -- | -- | Carter | Jack | 8242 | Chad Van Ripple | Administration | Accountant | Accounts Recievable | ||
| 7 | x | x | x | x | -- | -- | -- | Coleman | Ronnie | 8215 | Kevin Hame | Maintenance | Pipefitter | Pipe Fitter Apprentice | ||
| 8 | x | -- | x | -- | -- | -- | Deering | Agustin | 2143 | JackJones | Production | Technician 3 | C Shift | |||
Report | ||||||||||||||||
Now each month I get a Corporate report that I just copy and past it into Sheet2 in raw data format. This will contain raw data that is more up-to-date than my "Report" sheet. There will inevitably be a whole buch of minor and mojor differences, things like: name changes, job title changes, hirngs and firings, etc. I need that Corporate report to be compared to my live report and anywhere there is an excpeption, add the entire row to a 3rd sheet ("Exceptions") and highlight the thing(s) that were different. It ultimately ends up in generating an Exceptions Report that looks as follows:
| Excel 2003 | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Last name | First name | PersNo | Name of Manager (OM) | Organizational Unit | Position Description | Job Description | |||
| 2 | Adams | Penny | 6764 | Tony O'Donel | Production | Technician 1 | A Shift | |||
| 3 | Aparte | Isabel | 1429 | Jim Smith | Production | Technician 1 | B Shift | |||
| 4 | Bering | Charles | 9702 | Gerald Assmague | Maintenance | Machinist | Compressors | x | ||
| 5 | Coleman | Ronnie | 8215 | Mark Tennison | Maintenance | Pipefitter | Pipe Fitter Apprentice | |||
| 6 | Stevens | Mike | 1882 | Bill Smith | Maintenance | Machinist | Compressors | x | ||
| 7 | ||||||||||
Exceptions | ||||||||||
Now in real application, this may result in hundreds (if not more) lines of minor data changes. Excluding a complete addition (hire) or deletion (fire/quit) which I will flag and remove via the "x" in ColH on "Exceptions", I would like to draft another macro that compares employee numbers ("Report" ColJ and "Exceptions" ColC) and if there is a match, then copy the results from "Exceptions", ColA thru ColG and paste UNFORMATTED values into "Report" ColH thru ColN .
If possible, this would allow me to update my master sheet with the execution of a macro vs spending hours and hours of time going through each update 1 at a time.
Can someone help me with this?
