Compare 2 spreadsheets and highlight differences

1cyril1

Board Regular
Joined
Mar 31, 2003
Messages
89
I did a search for compare threads and there are numerous posts but I didn't manage to find one of the more simpler tasks covered - I'm sure they are hiding in there somewhere! If you can point me towards one that meets my needs or if you can suggest a solution via Reply post I'd be very grateful!

I've 2 spreadsheets as follows:

A) cols A to Z (a master doc that requires updating weekly)
B) cols A to G (updates downloaded from a work database) - all 7 cols have equivalent cols in sheetA though not adjacent

One of the cols in A and B contains the key field that's used to check for a match between the 2 sheets (in SheetA it's Col B and in SheetB it's Col A).

There could be over 4,000 records/rows in SheetA but SheetB will typically have less than 500 rows. I want to run a compare between the 2 sheets weekly and:


  1. Check each record in SheetB (ColA) against all of the records in SheetA (ColB) based on the key field.
  2. If there's no matching key in the weekly download (SheetB-ColA), anywhere in SheetA (ColB), then there is no updating to be done for that row and move on to the next record in B.
  3. Where a match is found on the key field in SheetA and SheetB, then the row in SheetB is to be highlighted (ideally the rows would be copied to a new worksheet as a record of that week's changes). Then, the other 6 fields in SheetB would replace the corresponding 6 fields in SheetA, even if only one of the fields is different. For the 6 non-key fields, let's assume that SheetB_Cols B to G correspond with SheetA_Cols C, E, G, I, K, M

I hope I've explained what's required reasonably clearly. I've used the terms record/row and field/cell interchangeably. The spreadsheets have yet to be created so I can't provide samples. However, I know the cols to be matched won't appear in the same order and I've used an illustrative example. To summarise the above:

If cell A1 in SheetB matches any ColB cell in Sheet A, then the data in B1, C1, D1, E1, F1 and G1 is highlighted and cells in the corresponding SheetA row are replaced by the SheetB data.

I look forward to any guidance you may be able to provide.

Many Thanks!
Cyril
 
sorry for the delay I found this very tricky

Con Number (key)Tr CodeCStart DateFin DateApproved ValueUsed ValueVen NumbVen NameBUREF
101NNNZZZNNNPPPAAAAAA
102AAYYYAAAAAAA
103AAXXXVVVAAAAAA
104AAAAAAAAAA
105AAAAAAAAAA
106AAAAAAAAAA
107AAAAAAAAAA
108AAWWWTTTSSSRRRAAAA
109AAAAAAAAAA
110AAAAAAAAAA
THE TABLE BELOW IS TODAYDATA.XLS AND FOR TEST PURPOSES A NUMBER OF A's WERE CHANGED
Con Number (key)Tr CodeCStart DateFin DateApproved ValueUsed ValueVen NumbVen NameBUREF
101NNNZZZNNNPPPAAAAAA
102AAYYYAAAAAAA
201AAAAAAAAAA
202AAAAAAAAAA
103AAXXXVVVAAAAAA
203AAAAAAAAAA
204AAAAAAAAAA
108AAWWWTTTSSSRRRAAAA
205AAAAAAAAAA
206AAAAAAAAAA
207AAAAAAAAAA
110AAAAAAAAAA
this macro, run from todaydata spreadsheet, finds all discrepancies and updates yesterday spreadsheet
coloring yellow changed cells in yesterday and in today the cells that were different in yesterday
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 22/11/2017 by bob
'
'
Windows("todaydata.xls").Activate
Range("A1:k13").Select
Selection.Copy
Windows("yesterdaydata.xls").Activate
Range("AA1:AK13").Select
ActiveSheet.Pasteputs a temporary copy of todaydata into the yesterday spreadsheet
For j = 2 To 13look at each row of the today data
For z = 2 To 11and compare with yesterday data [to see if there is a matching key]
If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15if a match found then compare all cells in that row with the today row cells
15 Next zif no match try next row in yesterday
GoTo 80if no match is found then look at the next row in the today data table
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
30 Cells(z, y) = Cells(j, y + 26)updates yesterday cell
Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End Withcolors yesterday cell
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End Withcolors corresponding cell in temporary today data table
40 Next y
80 Next j
Range("aa1:ak13").Select
Selection.Copy
Windows("todaydata.xls").Activate
Range("a1:k13").Select
ActiveSheet.Pasteputs the amended temporary today data table back into today spreadsheet
Windows("yesterdaydata.xls").Activate
Range("AA1:AK13").Select
Selection.ClearContents
Selection.Interior.ColorIndex = xlNonedeletes the temporary today table from the yesterday spreadsheet
Cells(1, 1).Select
999 End Sub

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col span="2"><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome back Bob and thanks for sticking with the project as it's not easy I know, especially now that we've reached Page 4:) For clarity (I hope) here's a recap:

(A) In post 15 I split the requirement into 2 sub-issues - Issue#1 was more complex as layouts were different; Issue#2 involves identical layouts (detail and sample in post#18).

(B) your new macro seems to be trying to solve across both issues and looks good but we need to take it one step at a time as below i.e. resolve Issue#2 before getting back to the initial post (aka 'Issue#1')

So to finalise Issue#2 (simple compare):

- no need to update anything, just highlight in each the records that don't match
- copy the non-matches in todaydata to a new sheet in the the todaydata workbook
- replace "Windows("todaydata.xls").Activate" with a full address as I don't have access to Windows directory ... previous macro bombed due to that (I think!) - try P:\Downloads instead? (I would try but not sure of syntax rules)

I think your macro covers most of the above - just needs some code removed and the address issue resolved (only refer to the previous posts referred to if you're completely stuck!!)

If we get that done and tested ok I can advise what is to happen to the records extracted to the new worksheet i.e. Issue#1 (will involve compare with another workbook with diff layout & updating cells where a match is found)

Here's hoping that's understandable - thanks again!!
 
Upvote 0
- copy the non-matches in todaydata to a new sheet in the the todaydata workbook

do you mean just the individual cells ie key 102 date1 ?

cull address do you mean something like f:\bob\todaydata.xls ?
 
Upvote 0
- copy the non-matches in todaydata to a new sheet in the the todaydata workbook

do you mean just the individual cells ie key 102 date1 ?

cull address do you mean something like f:\bob\todaydata.xls ?

I) copy the full row
ii) yes - use P:\Downloads\todaydata.xlsm ..... probably best to use .xlsm for both workbooks to ensure macro-enabled (for future if not all reqd now)

Good luck with that and thank you!
 
Upvote 0
as I don't have access to Windows directory

does this mean your company does not use windows - or that it is blocked to you ?
 
Upvote 0
My company uses Windows alright but we are not allowed access C:\Windows directory. Can we try the code for P:\Windows\**filename.xlsm** as above and see if that works - should do. I'm against the clock on this now Bob so appreciate closure this week if at all possible. If we don't succeed, at least we'll have given it a good shot i.e. you mostly!!
 
Upvote 0
If you open both workbooks and run this macro code as part of the macro we have rather than the windows refs, it works....

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 06/12/2017 by bob
'


'
ActiveWindow.ActivateNext
End Sub
 
Upvote 0
If you open both workbooks and run this macro code as part of the macro we have rather than the windows refs, it works....

Sub Macro7()
'
' Macro7 Macro
' Macro recorded 06/12/2017 by bob
'


'
ActiveWindow.ActivateNext
End Sub

I assume you mean to amend your macro from Post#31? I'm not clear where exactly so could you post the full amended macro please and I will test it asap?

Just to be clear, the macro needs to take account of the changes requested in my Post#32 as follows:

So to finalise Issue#2 (simple compare):

- no need to update anything, just highlight in each the records that don't match
- copy the non-matches in todaydata to a new sheet in the the todaydata workbook
- replace "Windows("todaydata.xls").Activate" with a full address as I don't have access to Windows directory - try P:\Downloads instead?
 
Upvote 0
Con Number (key)Tr CodeCStart DateFin DateApproved ValueUsed ValueVen NumbVen NameBUREF
101NNNZZZNNNPPPAAAAAA
102AAYYYAAAAAAA
201AAAAAAAAAA
202AAAAAAAAAA
103AAXXXVVVAAAAAA
203AAAAAAAAAA
204AAAAAAAAAA
108AAWWWTTTSSSRRRAAAA
205AAAAAAAAAA
206AAAAAAAAAA
207AAAAAAAAAA
110AAAAAAAAAA
just to make sure we are in agreement before I carry on
this macro now JUST highlights cells in today and yesterday if they differ
no cell has been updated
is a non match defined as a row with no cell highlighted ?
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 22/11/2017 by bob
'
'
Range("A1:k13").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("AA1:AK13").Select
ActiveSheet.Paste
For j = 2 To 13
For z = 2 To 11
If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15
15 Next z
GoTo 80
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
30 Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
40 Next y
80 Next j
Range("AA1:AK13").Select
Selection.Copy
ActiveWindow.ActivateNext
Cells(1, 1).Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("AA1:AK13").Select
Selection.ClearContents
999 End Sub

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Con Number (key)Tr CodeCStart DateFin DateApproved ValueUsed ValueVen NumbVen NameBUREF
101AAAAAAAAAA1
102AAAAAAAAAA1
103AAAAAAAAAA1
104AAAAAAAAAA
105AAAAAAAAAA
106AAAAAAAAAA
107AAAAAAAAAA
108AAAAAAAAAA1
109AAAAAAAAAA
110AAAAAAAAAA
above table is yesterday.xls
any non equal cells in today and yesterday are coloured yellow
the NEW last stage is to (with today table) put only rows with at least one non equal cell into sheet2 of today
I confirm that sheet2 of today workbook has only the rows where at least one discrepancy is showing
note both workbooks must be open before you run the macro
THE TABLE BELOW IS TODAYDATA.XLS
Con Number (key)Tr CodeCStart DateFin DateApproved ValueUsed ValueVen NumbVen NameBUREF
101NNNZZZNNNPPPAAAAAA
102AAYYYAAAAAAA
201AAAAAAAAAA
202AAAAAAAAAA
103AAXXXVVVAAAAAA
203AAAAAAAAAA
204AAAAAAAAAA
108AAWWWTTTSSSRRRAAAA
205AAAAAAAAAA
206AAAAAAAAAA
207AAAAAAAAAA
110AAAAAAAAAA
latest macro
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 22/11/2017 by bob
'
'
Range("A1:k13").Select
Selection.Copy
ActiveWindow.ActivateNext
Range("AA1:AK13").Select
ActiveSheet.Paste
For j = 2 To 13
For z = 2 To 11
If Cells(z, 1) = Cells(j, 27) Then GoTo 20 Else GoTo 15
15 Next z
GoTo 80
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
30 Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(z, 12) = 1
Cells(j, 38) = 1
40 Next y
80 Next j
Range("AA1:AL13").Select
Selection.Copy
ActiveWindow.ActivateNext
Cells(1, 1).Select
ActiveSheet.Paste
ActiveWindow.ActivateNext
Range("AA1:AL13").Select
Selection.ClearContents
ActiveWindow.ActivateNext
For myrow = 2 To 13
If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
100 Rows(myrow).Select
Selection.Copy
Sheets(2).Select
Rows(Cells(1, 17)).Select
ActiveSheet.Paste
Sheets(1).Select
200 Next myrow
Sheets(1).Select
999 End Sub

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="2"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top