Compare 2 spreadsheets and highlight differences

[1cyril1]

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

 

[1cyril1]

Cheers Bob, thanks for all of that! I've just tried on some real data in my office and ..... it has worked, except for copying changes to sheet2 in todaydata. Also, I've just over 4,000 rows in each workbook (sheet1) but there are no highlighted changes below Row 1998 in today and 1993 in yesterday ... is this limited by "Range("A1:k2000").Select" and other similar? I don't expect more than 10,000 over the next couple of years but some growth needs to be allowed for; that's why I was keen to get all rows referenced in previous posts as a foolproof approach (is impact on processing time the issue regardless of blank cells or full?).

The '1's allow me to filter and work with this solution for now at least - I've got 231 in todaydata and 220 in yesterday. I need to check some more of the highlighted items to ensure ok but the few I've looked at so far seem fine.

If you need any more feedback please let me know and I will try to reply before allowing you your Christmas break

Q: is =SUMPRODUCT(($A$1:$A$194<>"")*1) still needed in Z1 of yesterday? (showing '194' now ... Q1 showing '2000' and AQ1 has '0')
PS - my earlier note was to say I can't see highlighting in the sample data you posted - assume it doesn't stick after pasting in (not a prob!)

 

[oldbrewer]

change the 2000's to 15000's

I have no idea why you are not getting the changes in today sheet 2 - I am getting them perfectly - I will put in a diagnostic line that tells us what is going on when the "new" today is interrogated to find the rows with a 1 on the end and copy them to today sheet 2

As I say - it is working perfectly at this end....

 

[oldbrewer]

please run this changed macro - it should put a message in N1 of today sheet 1 that transfer to sheet 2 is about to commence.

Range("A1:k15000").Select
Selection.Copy
ActiveWindow.ActivateNext
'move to yesterday
Range("AA1:AK15000").Select
ActiveSheet.Paste
'put a copy of today in the yesterday workbook
For j = 2 To Cells(1, 43)
For z = 2 To Cells(1, 17)
Cells(30, 9) = j
Cells(31, 9) = z
'this shows how J and Z cycle (for diagnostic purposes)
For delay = 1 To 5000000: Next delay
If Cells(z, 1) = Cells(j, 27) Then GoTo 20
'checks if keys are the same
'if keys are not the same try the next key in yesterday
15 Next z
'if no key in yesterday matches, mark the temporary today sheet with a 1 in the last column
'also highlight the key in temp today data
Cells(j, 38) = 1
Cells(j, 27).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
'now run the next today key
GoTo 80
'keys match so now look at each cell in the rows
20 For y = 2 To 11
If Cells(z, y) = Cells(j, y + 26) Then GoTo 40 Else GoTo 30
'highlights both cells if there ia a discrepancy
30 Cells(z, y).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(z, 12) = 1
'marks the yesterday row with a 1
Cells(j, y + 26).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Cells(j, 38) = 1
'marks the row with a "1"
40 Next y
80 Next j
'copies the temporary today data
Range("AA1:AL15000").Select
Selection.Copy
ActiveWindow.ActivateNext
'move to today
Range("A1:L15000").Select
ActiveSheet.Paste
'overwrites the original today data with the "updated" temporary today from yesterday
ActiveWindow.ActivateNext
'move to yesterday
Range("AA1:AL15000").Select
Selection.ClearContents
'delete the temporary today data
ActiveWindow.ActivateNext
'move to today
Sheets(1).Select
For myrow = 2 To Cells(1, 17)
If Cells(myrow, 12) = 1 Then GoTo 100 Else GoTo 200
'following code copies only rows with at least one cell highlighted to sheet 2
100 Rows(myrow).Select
Cells(1, 14) = "starting to move rows to sheet 2": GoTo 999
Selection.Copy
Sheets(2).Select
'open sheet 2
Rows(Cells(1, 17)).Select
ActiveSheet.Paste
Sheets(1).Select
200 Next myrow
Sheets(2).Select
Range("m115000").Select
Selection.ClearContents
Cells(40, 1).Select
Sheets(1).Select
Cells(7, 14) = "COMPLETE"
Cells(7, 14).Select
999 End Sub

I have also allowed for 15000 rows of data....

 

[oldbrewer]

I just had a thought - how many sheets does today workbook have and what are there names ?

 

[1cyril1]

I just had a thought - how many sheets does today workbook have and what are there names ?

There are 2 sheets in todaydata now (Sheet1 and Sheet2) - I renamed them from Zxxx and Sheet1 respectively)

The new macro has been running for over 30 mins with most of my resources consumed by it (Outlook and others) so I've had to kill it unfortunately. Off the air now until the morning but may get another try then.

 

[oldbrewer]

just run it with 10 rows of data in yesterday and today

 

[oldbrewer]

Sheets(1).Select

will always select the first sheet in the workbook even it is named bob

in today sheet1 is new data

sheet2 will be used to list only changed rows

if Zxxx had new data then changed rows should have gone to sheet1

 

[1cyril1]

Ok, I've tried with 20 rows in each and the highlighting ran fine in both spreadsheets but nothing was copied to Sheet2 of todaydata (the headers are still in place there as well as the counter in Q1). I also note that the data remains in the temporary zone in AA:AL of Yesterday - may not be significant?

 

[oldbrewer]

you should have seen a message on today sheet 1 indicating copying was about to start - and nothing could go to sheet 2 yet - please try again and look on sheet 2

 

[oldbrewer]

look at N1 on today sheet 1