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
 
sheets(2) refers to the second sheet in the workbook - it might be named "claims" or "bob" or whatever - ok 2 workbooks it is - setting off on my travels in 5 minutes - call be Bob...............lol
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Value1KeyFieldTypeValue-XValue2Value-YDate1Date-XDate2Date-YSize1Size-XSize2Size-Y11
551221a32088822-Mar01-Jan04-Jun01-NovAAB1GGC625
222221242b433333922-Mar02-Jan17-Jun02-NovABABB2GTGTC737
13061303c513089024-Mar03-Jan06-Jun03-NovAAB3GGC849
571354d67777789112-Feb04-Jan25-Mar04-NovTTTTTB4SSSSSC9511
14921495f714989226-Mar05-Jan08-Jun05-NovAAB5GGC10613
1231n/an/an/an/an/an/an/an/an/an/an/an/an/a
1232n/an/an/an/an/an/an/an/an/an/an/an/an/a613
1233n/an/an/an/an/an/an/an/an/an/an/an/an/a
the above table is the data in bobmaster.xlsQ1:R6 is a lookup table
the correct fields in the keyfield 124 and 135 rows have been updatedthat picks out the odd columns to 13
no other fields were changed as the keyfields did not matchwithout of course 3 because
Type is not to be changed
KeyFieldValue1Value2Date1Date2Size1Size2
123n/an/an/an/an/an/a
124222223333322-Mar17-JunABABGTGT
125n/an/an/an/an/an/a
126n/an/an/an/an/an/a
30n/an/an/an/an/an/a
135577777712-Feb25-MarTTTTTSSSSS
138n/an/an/an/an/an/a
140n/an/an/an/an/an/a
the above table is the data in bobmatching.xls
in 124 row all cells have been coloured yellow
in 135 all except 25-Mar have been coloured yellow
I opened BOTH spreadsheets
and in bobmatching.xls I wrote the below macro,
and ran it from bobmatching.xls
Sub Macro8()
'
' Macro8 Macro
' Macro recorded 06/11/2017 by bob
'
'
Dim comps(10), mymaster(10)
Windows("bobmatching.xls").Activate
For a = 2 To 9
Windows("bobmatching.xls").Activate
mykey = Cells(a, 1)
Windows("bobmaster.xls").Activate
For b = 2 To 9
If Cells(b, 2) = mykey Then GoTo 10 Else GoTo 150
10 Windows("bobmatching.xls").Activate
For c = 2 To 7
counter = counter + 1
comps(counter) = Cells(a, c)
Next c
counter = 0
Windows("bobmaster.xls").Activate
mymaster(1) = Cells(b, 1)
mymaster(2) = Cells(b, 5)
mymaster(3) = Cells(b, 7)
mymaster(4) = Cells(b, 9)
mymaster(5) = Cells(b, 11)
mymaster(6) = Cells(b, 13)
For z = 1 To 6
If comps(z) <> mymaster(z) Then GoTo 50 Else GoTo 100
50 Cells(8, 17) = z
Cells(b, Cells(8, 18)) = comps(z)
Windows("bobmatching.xls").Activate
Cells(a, z + 1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Windows("bobmaster.xls").Activate
100 Next z
GoTo 200
150 Next b
200 Next a
999 End Sub

<colgroup><col span="14"><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
 
Upvote 0
Welcome back Bob and thanks for the additional work! I can't wait to try out but might not get to it for a few days - will let you know how I get on!

Regards, Cyril
 
Upvote 0
Value1
KeyField
Type
Value-X
Value2
Value-Y
Date1
Date-X
Date2
Date-Y
Size1
Size-X
Size2
Size-Y
1
1
55
122
1a
3
20
888
22-Mar
01-Jan
04-Jun
01-Nov
AA
B1
GG
C6
2
5
22222
124
2b
4
33333
9
22-Mar
02-Jan
17-Jun
02-Nov
ABAB
B2
GTGT
C7
3
7
1306
130
3c
5
130
890
24-Mar
03-Jan
06-Jun
03-Nov
AA
B3
GG
C8
4
9
57
135
4d
6
77777
891
12-Feb
04-Jan
25-Mar
04-Nov
TTTTT
B4
SSSSS
C9
5
11
1492
149
5f
7
149
892
26-Mar
05-Jan
08-Jun
05-Nov
AA
B5
GG
C10
6
13
1231
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
1232
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
6
13
1233
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
n/a
the above table is the data in bobmaster.xls
Q1:R6 is a lookup table
the correct fields in the keyfield 124 and 135 rows have been updated
that picks out the odd columns to 13
no other fields were changed as the keyfields did not match
without of course 3 because
Type is not to be changed
KeyField
Value1
Value2
Date1
Date2
Size1
Size2
123
n/a
n/a
n/a
n/a
n/a
n/a
124
22222
33333
22-Mar
17-Jun
ABAB
GTGT
125
n/a
n/a
n/a
n/a
n/a
n/a
126
n/a
n/a
n/a
n/a
n/a
n/a
30
n/a
n/a
n/a
n/a
n/a
n/a
135
57
77777
12-Feb
25-Mar
TTTTT
SSSSS
138
n/a
n/a
n/a
n/a
n/a
n/a
140
n/a
n/a
n/a
n/a
n/a
n/a
the above table is the data in bobmatching.xls
in 124 row all cells have been coloured yellow
in 135 all except 25-Mar have been coloured yellow
I opened BOTH spreadsheets
and in bobmatching.xls I wrote the below macro,
and ran it from bobmatching.xls
Sub Macro8()
'
' Macro8 Macro
' Macro recorded 06/11/2017 by bob
'
'
Dim comps(10), mymaster(10)
Windows("bobmatching.xls").Activate
For a = 2 To 9
Windows("bobmatching.xls").Activate
mykey = Cells(a, 1)
Windows("bobmaster.xls").Activate
For b = 2 To 9
If Cells(b, 2) = mykey Then GoTo 10 Else GoTo 150
10 Windows("bobmatching.xls").Activate
For c = 2 To 7
counter = counter + 1
comps(counter) = Cells(a, c)
Next c
counter = 0
Windows("bobmaster.xls").Activate
mymaster(1) = Cells(b, 1)
mymaster(2) = Cells(b, 5)
mymaster(3) = Cells(b, 7)
mymaster(4) = Cells(b, 9)
mymaster(5) = Cells(b, 11)
mymaster(6) = Cells(b, 13)
For z = 1 To 6
If comps(z) <> mymaster(z) Then GoTo 50 Else GoTo 100
50 Cells(8, 17) = z
Cells(b, Cells(8, 18)) = comps(z)
Windows("bobmatching.xls").Activate
Cells(a, z + 1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Windows("bobmaster.xls").Activate
100 Next z
GoTo 200
150 Next b
200 Next a
999 End Sub

<tbody>
</tbody>

At last I got to try it out. In my work situation, a lot of the IT resource is virtual (on a Citrix farm many miles away) so things may not always be as they appear! For example, the only Windows folder I'm allowed to view/access is in my P:\Windows area unlike the normal C:\ location. I'm not sure if relevant but wanted to fill you in just in case.

I needed to save bobmatching as .xlsm to enable your macro and decided to do this for bobmaster also; I then changed all .xls refs in macro to .xlsm. When I ran the macro I hit a "run-time error '1004'" which seems to be unhappy with "Cells(b, Cells(8, 18)) = comps(Z)" ..... not sure if that's the only issue or not. Any ideas?

Thanks Bob!
 
Upvote 0
Bob, I was going to PM you for advice but note you don't accept private mails.

Pending full resolution of the main issue in my post, I was going to ask for related help in comparing 2 identically formatted 'bobmatching' spreadsheets (diff docs) and highlighting differences. This would be based on the match field being Col A in both spreadsheets and us checking what's changed from the older doc to the newer one [A] (downloaded data from say a week apart). All changes to be highlighted in [A] .... incl. records not found in the older spreadsheet .

I don't want to hog Board time so hoping this is appropriate ..... not sure of protocol:confused: I will need a solution separately if not possible within this thread and can do as a new thread if needs be .... please advise best course. That will be all I need for some time I hope!!
 
Upvote 0
My PM facility was removed by the mods. Tell us what you want with a simplified example, eg I have 2 nearly identical spreadsheets and I want to highlight (or list) the differences eg typos....ie if B different highlight A and if not found in B at all still highlight A
 
Upvote 0
Ok Bob - the 2nd issue is really straightforward:

I need to compare a monthly download file with the previous download file - same layout/format as shown here (hope you can view?)

The match column is Col-A and there won't be duplicates

Where there's a match in Col-A, I need to highlight Cols E, F, G, H and I but ..... only if the entry has changed

That would be a good first step - is VLOOKUP the answer?

In the 2nd step, I'd like all changed rows in the most recent file to be copied to a new 'Changes' worksheet in that file.

If you need any more please let me know - thanks a mill for your patience.

(P.S. If you have any feedback on the macro error in my original request you might advise separately - it's a lesser priority right now)


Have a good weekend!
Cyril
 
Upvote 0
Ok Bob - the 2nd issue is really straightforward:

I need to compare a monthly download file with the previous download file - same layout/format as shown here (hope you can view?) - 1st row added by me just to show Col letter involved.

The match column is Col-A and there won't be duplicates

Where there's a match in Col-A, I need to highlight Cols E, F, G, H and I but ..... only if the entry has changed

That would be a good first step - is VLOOKUP the answer?

In the 2nd step, I'd like all changed rows in the most recent file to be copied to a new 'Changes' worksheet in that file.

If you need any more please let me know - thanks a mill for your patience.

(P.S. If you have any feedback on the macro error in my original request you might advise separately - it's a lesser priority right now)


Have a good weekend!
Cyril

Mea culpa - I've edited the post as the original link may not work outside of my org after all - it's now amended above but you can also try this (Download-Sept2017);)
 
Last edited:
Upvote 0
I lost track of where we are so went back a step - if the principle of below is wrong please tell me

keydate1date2date3date4date5date6date2date7date4date8
id103/01/201720/02/201709/04/201727/05/201714/07/2017id3321/01/201710/03/201727/04/201714/06/201701/08/2017
id212/01/201701/03/201718/04/201705/06/201723/07/2017id430/01/201719/03/201706/05/201701/06/201710/08/2017
id321/01/201710/03/201727/04/201714/06/201701/08/2017id5508/02/201728/03/201715/05/201702/07/201719/08/2017
id430/01/201719/03/201706/05/201701/06/201710/08/2017id617/02/201706/04/201724/05/201711/07/201728/08/2017
id508/02/201728/03/201715/05/201702/07/201719/08/2017id726/02/201701/04/201702/06/201720/07/201706/09/2017
id617/02/201706/04/201724/05/201711/07/201728/08/2017id8807/03/201724/04/201711/06/201729/07/201715/09/2017
id726/02/201701/04/201702/06/201720/07/201706/09/2017id9916/03/201703/05/201720/06/201707/08/201724/09/2017
id807/03/201724/04/201711/06/201729/07/201715/09/2017id1025/03/201712/05/201729/06/201716/08/201703/10/2017
id916/03/201703/05/201720/06/201707/08/201724/09/2017id11103/04/201721/05/201708/07/201725/08/201712/10/2017
id1025/03/201712/05/201729/06/201716/08/201703/10/2017
id1103/04/201721/05/201708/07/201725/08/201712/10/2017
id1212/04/201730/05/201717/07/201703/09/201721/10/2017
id1321/04/201708/06/201726/07/201712/09/201730/10/2017
id1430/04/201717/06/201704/08/201721/09/201708/11/2017problem statement
id1509/05/201726/06/201713/08/201730/09/201717/11/2017
id1618/05/201705/07/201722/08/201709/10/201726/11/2017check each id in the smaller table on the right
id1727/05/201714/07/201731/08/201718/10/201705/12/2017against the ids in the master (left) table
id1805/06/201723/07/201709/09/201727/10/201714/12/2017
id1914/06/201701/08/201718/09/201705/11/201723/12/2017where there is a match determine if dates 2 or 4 or both are different
id2023/06/201710/08/201727/09/201714/11/201701/01/2018
if they are, modify the master table and highlight the date in the right table
when this process is sorted, amend to allow for 2 different workbooks
this macro detected id4 date 4 and id7 date 2 were different
it changed them in the master table
and highlighted them yellow in the right hand table
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 18/11/2017 by bob
'
'
For j = 2 To 10
For k = 2 To 21
If Cells(k, 1) = Cells(j, 9) Then GoTo 20 Else GoTo 50
20 If Cells(k, 3) <> Cells(j, 11) Then GoTo 25 Else GoTo 30
25 Cells(k, 3) = Cells(j, 11)
Cells(j, 11).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
30 If Cells(k, 5) <> Cells(j, 13) Then GoTo 35 Else GoTo 50
35 Cells(k, 5) = Cells(j, 13)
Cells(j, 13).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
50 Next k
Next j
End Sub

<colgroup><col><col span="2"><col span="2"><col><col span="3"><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
I lost track of where we are so went back a step - if the principle of below is wrong please tell me

keydate1date2date3date4date5date6date2date7date4date8
id103/01/201720/02/201709/04/201727/05/201714/07/2017id3321/01/201710/03/201727/04/201714/06/201701/08/2017
id212/01/201701/03/201718/04/201705/06/201723/07/2017id430/01/201719/03/201706/05/201701/06/201710/08/2017
id321/01/201710/03/201727/04/201714/06/201701/08/2017id5508/02/201728/03/201715/05/201702/07/201719/08/2017
id430/01/201719/03/201706/05/201701/06/201710/08/2017id617/02/201706/04/201724/05/201711/07/201728/08/2017
id508/02/201728/03/201715/05/201702/07/201719/08/2017id726/02/201701/04/201702/06/201720/07/201706/09/2017
id617/02/201706/04/201724/05/201711/07/201728/08/2017id8807/03/201724/04/201711/06/201729/07/201715/09/2017
id726/02/201701/04/201702/06/201720/07/201706/09/2017id9916/03/201703/05/201720/06/201707/08/201724/09/2017
id807/03/201724/04/201711/06/201729/07/201715/09/2017id1025/03/201712/05/201729/06/201716/08/201703/10/2017
id916/03/201703/05/201720/06/201707/08/201724/09/2017id11103/04/201721/05/201708/07/201725/08/201712/10/2017
id1025/03/201712/05/201729/06/201716/08/201703/10/2017
id1103/04/201721/05/201708/07/201725/08/201712/10/2017
id1212/04/201730/05/201717/07/201703/09/201721/10/2017
id1321/04/201708/06/201726/07/201712/09/201730/10/2017
id1430/04/201717/06/201704/08/201721/09/201708/11/2017problem statement
id1509/05/201726/06/201713/08/201730/09/201717/11/2017
id1618/05/201705/07/201722/08/201709/10/201726/11/2017check each id in the smaller table on the right
id1727/05/201714/07/201731/08/201718/10/201705/12/2017against the ids in the master (left) table
id1805/06/201723/07/201709/09/201727/10/201714/12/2017
id1914/06/201701/08/201718/09/201705/11/201723/12/2017where there is a match determine if dates 2 or 4 or both are different
id2023/06/201710/08/201727/09/201714/11/201701/01/2018
if they are, modify the master table and highlight the date in the right table
when this process is sorted, amend to allow for 2 different workbooks
this macro detected id4 date 4 and id7 date 2 were different
it changed them in the master table
and highlighted them yellow in the right hand table
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 18/11/2017 by bob
'
'
For j = 2 To 10
For k = 2 To 21
If Cells(k, 1) = Cells(j, 9) Then GoTo 20 Else GoTo 50
20 If Cells(k, 3) <> Cells(j, 11) Then GoTo 25 Else GoTo 30
25 Cells(k, 3) = Cells(j, 11)
Cells(j, 11).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
30 If Cells(k, 5) <> Cells(j, 13) Then GoTo 35 Else GoTo 50
35 Cells(k, 5) = Cells(j, 13)
Cells(j, 13).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
50 Next k
Next j
End Sub

<tbody>
</tbody>

Bob - you are on broadly the right lines but I have managed to confuse you so let me try to summarise with ref to the post numbers. There are 2 related but different requirements as follows:

Issue#2 first as it has become more of a priority for me. Post#17 (17th Nov) outlines what's involved using a sample of real data in the linked doc. It's quite straightforward .... at least I hope:rolleyes:

Issue#1 ... this can await a resolution of Issue#2 (you were nearly there with the macro in your Post#12 but alas it bombed on me and I'm not sure why (error as in my Post#14). The main difference here is that the layout of the doc to be updated differs (in Issue#2 the 2 docs are identical in layout/format terms). Let's park for now to avoid any confusion?

Continued thanks ..... Cyril
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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