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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
tomatolarge25pepperlarge32X
lettucelarge39tomatomedium20ITEMS MARKED X FOR CLARITY
pepperlarge32tomatosmall19Xneed to be updated in main table
tomatomedium20lettucesmall29
lettucemedium32peppersmall24X
peppermedium27
tomatosmall19
lettucesmall29
peppersmall24
original main table
tomatolarge25
this macro has updated changed priceslettucelarge39
pepperlarge30
Sub Macro5()tomatomedium20
'lettucemedium32
' Macro5 Macropeppermedium27
' Macro recorded 27/10/2017 by bobtomatosmall18
'lettucesmall29
peppersmall25
'
For j = 1 To 5
For k = 1 To 9
If Cells(k, 1) = Cells(j, 9) Then GoTo 20 Else GoTo 99
20 If Cells(k, 2) = Cells(j, 10) Then GoTo 70 Else GoTo 99
70 If Cells(k, 3) <> Cells(j, 11) Then GoTo 80 Else GoTo 99
80 Cells(k, 3) = Cells(j, 11)
99 Next k
Next j
End Sub

<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>
</tbody>
 
Upvote 0
Thanks Oldbrewer for taking the time to post your reply. Unfortunately, I'm not able to follow - can you elaborate please?
 
Upvote 0
Thanks again .... I accept it's not so easy to convey the requirement via text alone so I've spent a good bit of time creating a demo doc that relates to my original post. The only additional point is that a record in SheetB will only ever find a single match in the master doc SheetA. Please see if you can access this public doc (link) and if it makes things clearer for you. I hope so anyway:confused: It may look busy at first but there's very little going on in reality as only 2 records can be matched across the 2 sheets and they are all that we need to focus on. If these can be isolated in a separate sheet after the updating is finished it would be a bonus i.e. not essential.

P.S. I did try using one of the recommended add-ins but it distorted col widths, some formats and dropped text boxes when I tried on the test site)

P.S. #2 ..... what's your opinion of Vlookup as an alternative option?
 
Upvote 0
SHEET-A (Master Doc)
Value1KeyFieldTypeValue-XValue2Value-YDate1Date-XDate2Date-YSize1Size-XSize2Size-Y
551221a32088822-Mar01-Jan04-Jun01-NovAAB1GGC6
9991242b488888922-Mar02-Jan17-Jun02-NovABB2GGGGC7
571303c53089024-Mar03-Jan06-Jun03-NovAAB3GGC8
571354d633389112-Feb04-Jan25-Mar04-NovAAAAAAB4GGC9
591495f73089226-Mar05-Jan08-Jun05-NovAAB5GGC10
60n/an/an/an/an/an/an/an/an/an/an/an/an/a
61n/an/an/an/an/an/an/an/an/an/an/an/an/a
62n/an/an/an/an/an/an/an/an/an/an/an/an/a
SHEET-B (Downloaded file for matching against the Master Doc)
KeyFieldValue1Value2Date1Date2Size1Size2this macro has detected
123n/an/an/an/an/an/a
12499988822-Mar17-JunABGGGGB18=999 C18 = 888 E18=17-Jun G18=GGGG
125n/an/an/an/an/an/a
126n/an/an/an/an/an/a
30n/an/an/an/an/an/a
1355733312-Feb25-MarAAAAAAGGC22=333 D22=12-Feb F22=AAAAAA
138n/an/an/an/an/an/a
140n/an/an/an/an/an/a
and has coloured each of those cells yellow
and updated the master table at the top
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 28/10/2017 by bob
'
'
For j = 17 To 24
For k = 5 To 12
If Cells(j, 1) = Cells(k, 2) Then GoTo 20 Else GoTo 50
20 y = -1
For z = 2 To 7
22 y = y + 2
If y = 3 Then GoTo 22
If Cells(j, z) = Cells(k, y) Then GoTo 40
Cells(k, y) = Cells(j, z)
Cells(j, z).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
40 Next z
50 Next k
Next j
End Sub

<colgroup><col span="14"><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Thanks a lot Oldbrewer! Sorry about the delay in responding following a public holiday here on Monday and work priorities in meantime.

As one that is only broadly familiar with macros, can I ask where in the macro the 2 separate sheets are referenced (In my sample, I had included both in the one tab just for ease of viewing)? If I used 2 spreadsheets rather than 2 worksheets, what area of the macro would need adjusting? Is the macro stored in one spreadsheet or more widely available to all?

The results seem to be along the lines I'm looking for, even if the colour isn't showing on the post, so I'm keen to check out. All going well I'm hoping I won't be troubling you much further on this.

Much obliged - Cyril
 
Upvote 0
I usually start with all data on one sheet to sort out the approach. Then when you the client say it works and now can we take account of different sheets I finish it off. Is it 2 sheets in the same workbook ie sheet1 is the master and sheet 2 the updates sheet ?

I am retired, aged 72, and helping people on here keeps my brain ticking over, and so far I have never watched daytime TV.....

Bob
 
Upvote 0
Value1KeyFieldTypeValue-XValue2Value-YDate1Date-XDate2Date-YSize1Size-XSize2Size-YKeyFieldValue1Value2Date1Date2Size1Size2
551221a32088822-Mar01-Jan04-Jun01-NovAAB1GGC6123n/an/an/an/an/an/a
9991242b488888922-Mar02-Jan17-Jun02-NovABB2GGGGC7124222223333322-Mar17-JunABABGTGT
571303c53089024-Mar03-Jan06-Jun03-NovAAB3GGC8125n/an/an/an/an/an/a
571354d633389112-Feb04-Jan25-Mar04-NovAAAAAAB4GGC9126n/an/an/an/an/an/a
591495f73089226-Mar05-Jan08-Jun05-NovAAB5GGC10MATCHING SHEET30n/an/an/an/an/an/a
60n/an/an/an/an/an/an/an/an/an/an/an/an/aTO SEE135577777712-Feb25-MarTTTTTSSSSS
61n/an/an/an/an/an/an/an/an/an/an/an/an/aWHICH VALUES138n/an/an/an/an/an/a
62n/an/an/an/an/an/an/an/an/an/an/an/an/aARE TO140n/an/an/an/an/an/a
BE CHANGED
this is the first sheet, master
the second sheet is matching
this macro is run from within matching sheet
IT DOES NOT WORK
BUT I CANNOT WORK ON IT FOR 5 DAYS
MAYBE YOU LOOK AT IT AND TRY TO SORT IT
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 28/10/2017 by bob
'
'
Dim mymatch(10, 10)
For j = 2 To 9
Sum = Sum + 1
For k = 1 To 7
mymatch(Sum, k) = Cells(j, k)
Next k
Next j
Sheets(1).Select
For p = 2 To 9
For r = 1 To 13 Step 2
If r = 3 Then GoTo 90
rr = rr + 1
tot = tot + 1
If Cells(p, 1) = mymatch(p - 1, tot) Then GoTo 80 Else GoTo 100
80 If Cells(p, r) <> mymatch(p - 1, rr) Then GoTo 85 Else GoTo 90
85 Cells(p, r) = mymatch(p - 1, rr)
Sheets(2).Select
Cells(p - 1, rr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Sheets(1).Select
90 Next r
100 Next p
End Sub

<colgroup><col span="14"><col><col><col><col><col span="10"></colgroup><tbody>
</tbody>
 
Upvote 0
Value1KeyFieldTypeValue-XValue2Value-YDate1Date-XDate2Date-YSize1Size-XSize2Size-YKeyFieldValue1Value2Date1Date2Size1Size2
551221a32088822-Mar01-Jan04-Jun01-NovAAB1GGC6123n/an/an/an/an/an/a
9991242b488888922-Mar02-Jan17-Jun02-NovABB2GGGGC7124222223333322-Mar17-JunABABGTGT
571303c53089024-Mar03-Jan06-Jun03-NovAAB3GGC8125n/an/an/an/an/an/a
571354d633389112-Feb04-Jan25-Mar04-NovAAAAAAB4GGC9126n/an/an/an/an/an/a
591495f73089226-Mar05-Jan08-Jun05-NovAAB5GGC10MATCHING SHEET30n/an/an/an/an/an/a
60n/an/an/an/an/an/an/an/an/an/an/an/an/aTO SEE135577777712-Feb25-MarTTTTTSSSSS
61n/an/an/an/an/an/an/an/an/an/an/an/an/aWHICH VALUES138n/an/an/an/an/an/a
62n/an/an/an/an/an/an/an/an/an/an/an/an/aARE TO140n/an/an/an/an/an/a
BE CHANGED
this is the first sheet, master
the second sheet is matching
this macro is run from within matching sheet
IT DOES NOT WORK
BUT I CANNOT WORK ON IT FOR 5 DAYS
MAYBE YOU LOOK AT IT AND TRY TO SORT IT
Sub Macro4()
'
' Macro4 Macro
' Macro recorded 28/10/2017 by bob
'
'
Dim mymatch(10, 10)
For j = 2 To 9
Sum = Sum + 1
For k = 1 To 7
mymatch(Sum, k) = Cells(j, k)
Next k
Next j
Sheets(1).Select
For p = 2 To 9
For r = 1 To 13 Step 2
If r = 3 Then GoTo 90
rr = rr + 1
tot = tot + 1
If Cells(p, 1) = mymatch(p - 1, tot) Then GoTo 80 Else GoTo 100
80 If Cells(p, r) <> mymatch(p - 1, rr) Then GoTo 85 Else GoTo 90
85 Cells(p, r) = mymatch(p - 1, rr)
Sheets(2).Select
Cells(p - 1, rr).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Sheets(1).Select
90 Next r
100 Next p
End Sub

<tbody>
</tbody>
Oldbrewer, people like you are so generous with your time and talents!!

I will await your return rather than dabble in something I'm pretty clueless about. For instance, I don't know if 'Sheets(2)' refers to the 'master' sheet or not .... if not, then where is the link?

On reflection, I think I will opt for 2 completely separate spreadsheets (workbooks) - you can refer to them as Sheet-A (master) and Sheet-B (download doc) to keep aligned with the text in posts and in the sample doc here.

'til the next time then ..... take care!
Cyril
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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