Comparing 2 Worksheets and Updating Rows Where Differences were Found

[briggss1]

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.

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?

 

[Fazza]

hi,

This looks to me like it would be best done in a database. Some queries to identify differences and then UPDATE queries to make changes.

I think an approach in Excel would be not as good. It could be along the lines of this old post where I used some SQL to do something similar http://www.mrexcel.com/forum/showthread.php?238791

HTH. regards

 

[briggss1]

Any other thoughts on codes specific to the examples above? I have this all set up in an Excel sheet already. I guess I can look at copyinbg this stuff over to a database (access), but my ignorance level is exponentially increased in database world. I at least can understand the codes in Excel, just can't create them as I have no true code writing education.

 

[Fazza]

hi. The highlighting in the exceptions report is simple enough - it can be readily done with normal conditional format & formula such as MATCH/INDEX. Below is some sample code to update existing records in "Report" from "Exceptions". Really just a one liner. Just add another query to append the new records. As you can see, this is simple with a database approach. cheers

  Dim objRS As Object

  Set objRS = CreateObject("ADODB.Recordset")

  objRS.Open Join$(Array( _
    "UPDATE [Report$] R", _
    "INNER JOIN [Exceptions$] E ON R.PersNo = E.PersNo", _
    "SET R.[Last name] = E.[Last name], R.[First name] = E.[First name], R.[Name of Manager (OM)] = E.[Name of Manager (OM)], R.[Organizational Unit] = E.[Organizational Unit], R.[Position Description] = E.[Position Description], R.[Job Description] = E.[Job Description]"), vbCr), _
    "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""

  Set objRS = Nothing

 

[Fazza]

I had some more time to look at this. Here is the extra line to add the new record(s). I've changed to a connection object instead of a recordset. Cheers

'update existing records on worksheet "Report" based on "Exceptions" worksheet
'add new records to worksheet "Report" based on "Exceptions" worksheet

Dim objConn As Object

Set objConn = CreateObject("ADODB.Connection")

With objConn
  .Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""
  .Execute "UPDATE [Report$] R INNER JOIN [Exceptions$] E ON R.PersNo = E.PersNo SET R.[Last name] = E.[Last name], R.[First name] = E.[First name], R.[Name of Manager (OM)] = E.[Name of Manager (OM)], R.[Organizational Unit] = E.[Organizational Unit], R.[Position Description] = E.[Position Description], R.[Job Description] = E.[Job Description]"
  .Execute "INSERT INTO [Report$] SELECT E.* FROM [Exceptions$] E LEFT OUTER JOIN [Report$] R ON E.PersNo = R.PersNo WHERE R.PersNo Is Null"
  .Close
End With

Set objConn = Nothing

 

[briggss1]

So I added this script to my existing macro and it shows an error on the following line and then goes into Debug mode:

.Execute "UPDATE [Report$] R INNER JOIN [Exceptions$] E ON R.PersNo = E.PersNo SET R.[Last name] = E.[Last name], R.[First name] = E.[First name], R.[Name of Manager (OM)] = E

One thing I had to do is rename "Report" to "Active". I am not sure what the functionality of the "E" and "R" are throughout the code, but I left that alone and then tried changing the "R" to "A" assuming they may be the first abbreviation of the worksheet's name. In any case, neither mode worked.

 

[Fazza]

My understanding : the code I posted worked perfectly until you changed a worksheet name.

You changed a worksheet name from "Report" to "Active" and also changed the VBA : now it doesn't work.

The solution should be to simply to find "Report" & replace with "Active" - obviously starting from the original working solution.

If that doesn't work (and I don't know why that would be), then please revert to the earlier set up that did work.

 

[chrispegg]

Fazza, Hello... hope you can help me (apologies, I'm a bit green when it comes to VBA, so bear with me...)

I've added the above VBA code to the Macro I am writing, and changed the references according to the spreadsheet names I want to run the update/additions against.

One of the problems I foresee with this is that the existing "report" spreadsheet name changes every month (with potentially several revisions within each month). The existing report also has multiple sheets within the workbook.

In this case:

Existing workbook:
"MOR Oct-2012 v3"
Worksheet:
"Cognos Export"

New Extract Workbook:
"cognos.cgi"
New Extract Worksheet:
"Main-1"

As a test, I tried running the VBA against a couple of headings, however the connection failed. I suspect this is due to the workbook name, however I won't be able to automate based on the existing workbook name (due to it changing)... Is there any way around this?

Sub Macro1()
'update existing records on worksheet "Cognos Export" based on "congnos.cgi" worksheet
'add new records to worksheet "Cognos Export" based on "congnos.cgi" worksheet
Dim objConn As Object
Set objConn = CreateObject("ADODB.Connection")
With objConn
.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;"""
.Execute "UPDATE [Cognos Export$] CE INNER JOIN [cognos.cgi$] cgi ON CE.EMS # = cgi.EMS # SET CE.[Month] = cgi.[Month], CE.[Date] = cgi.[Date]"



.Execute "INSERT INTO [Cognos Export$] SELECT cgi.* FROM [cognos.cgi$] cgi LEFT OUTER JOIN [Report$] CE ON cgi.EMS # = CE.EMS # WHERE CE.EMS # Is Null"
.Close
End With
Set objConn = Nothing

End Sub

Legend:
cgi = New Extract (cognos.cgi)
CE = Existing report (Cognos Export)
Primary Key = "EMS #"

Thanks again for your time,

Chris

 

[Fazza]

hi, Chris

1. You've added the code to a macro you're writing. I don't know where that code sits. This may or may not be an issue. Just understand the code refers to ActiveWorkbook, and opens a connection to it. The original code (early in the thread) was within a single Excel file for which the update was from one of its worksheets to another of its worksheets.
2. You've changed the references according to the spreadsheet names you're using. I don't see that so am unsure if that is OK or not.
3. You foresee a problem with the existing report spreadsheet name changing every month, with potentially several revisions within each month. That should be easy to accommodate in the code.
4. Your existing report has multiple sheets within the workbook. That should be no problem so long as you can identify which sheet to address.
5. You've tested and the connection failed. I don't have enough clarity of the set up to check. I assume you are using Excel 2003. The ".open" line ends with Excel 8.0, if you're not using Excel 2003 that needs to change. Google for connection strings if you want further info. If the workbook name, as you assume, is the problem, then I don't know enough about what you're doing. For example point 1 above.
6. When field names contain spaces either refer to them like `field name` or [field name]. The latter is considered more robust. So, EMS # is no good. Change to [EMS #]
7. Field names to avoid normally include the two you've used, Month and Date. You may be OK so long as you explicitly identify them always as fields - so enclosed within brackets - and more particularly always include an explicit table reference. Normal good practice though is to not have field names Month or Date. This for info, you can probably keep it as is if you really want to.
8. cognos.cgi is given as a workbook name. The SQL refers to it as a worksheet name??
9. The UPDATE query has [cognos.cgi$] - if that is the workbook name, this will not work. It needs to be something like `workbook reference.xls`.[worksheet name$] though you only need the full workbook reference if it is different from the file to which you've connected. OK?
10. The INSERT query has similar.

There might be other things but that is most of it. Though it might seem like a lot, there probably is not too much wrong.

HTH. Regards

 

[chrispegg]

I'll have a look through these points and rectify anything I can see that is wrong, compared to your notes. I listed the workbook and worksheet names above the VBA I posted, so:

Existing workbook ref would be 'MOR Oct-2012 v3'.[Cognos Export$]
Revised extract ref would be 'cognos.cgi'.[Main-1$]

As you pointed out, EMS # should be [EMS #] (which I should have known... sorry )
I'm using Excel 2010 - I'll find the connection string for that from Google as suggested. I understood all of the logic behind the VBA you provided, with the exception of the connection properties, which I have not used before.

The VBA in question here is segregated from other code, as I'm just testing the properties of this section of the VBA against the two data lists with a few column headers to ensure it's working.

Thanks again for your help Fazza, it is much appreciated!