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

 

[Roderick_E]

I meant different numbers of rows in each sheet

Yes IF you have a UID (unique identifier it can check against on both sheets)

 

[1cyril1]

Hi again, I'm back!

I just tried with the new line added at the start (as advised) and .... the macro fails to run at all. The good news is that I commented it out and ran on 2 spreadsheets with 30 rows in each. This time, I get the expected entries in N1 ('starting to move rows to sheet 2') and N7 ('complete'). The macro ends in yesterday with cols AA:AL selected (with highlights in yellow). Changes seem to be highlighted ok in both spreadsheets. Alas, nothing gets copied to sheet#2 of todaydata - cursor lands on cell A40 there! (this test was done in Windows).

I guess that's progress and reassuring. Is it worth trying one more time to get the sheet#2 issue, great if that could be cracked - appreciate we're now on page#10 of this thread though so not too expectiant .... #embarassing

insert this row at the start before the range statement

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 18/12/2017 by bob
'


'
Cells(1, 14) = "starting to move rows to sheet 2": GoTo 999
Range("A1:k15000").Select

this will flush out the issue - I hope

if the message appears I will put it further into the code to see why "you" are not getting to that row (ie preparing to populate today sheet 2)

 

[1cyril1]

Thank you Roderick! I had stumbled on that 'compare' option during my initial research but couldn't find the 'Inquire' menu it on either my work system or on my Mac or on Office365 in the cloud. I checked further this week and unexpectedly managed to get the plugin added and ...... wow, it's a fab little utility, amazingly quick. It would go a long way to help with my requirement but Bob's super macro gives me more including an easy method of extracting changed records to a new sheet etc. The macro may also be better for highlighting records in one workbook that aren't in the other etc. I haven't had time to test much so may be missing something (trying to solve this in my very scarce spare time .... as you can probably tell!). It's certainly a great new addition to my Excel locker so thanks a mill

 

[Roderick_E]

1cyril1 -- glad you got it working. I don't have access to test on Mac or Office365. The add-in part is still fairly new. Glad you got it working. Yes, it is more to simply compare differences by highlighting. I'm really digging the new TOC (table of contents) Maker I added on Dec 27 version. And the Columns by Color is a life saver for a sheet with lots of columns.

 

[oldbrewer]

and .... the macro fails to run at all.

macros always run - it was ment to put a message in cells(1,14) ie N1

you need to repeat and tell me - if you do not get a message in N1 (in today, sheet 1) - if you don't there is a fundamental problem....

Bob

 

[1cyril1]

Sincere apologies Bob, I've been waiting to retest and provide you with an authoritative overview. I haven't had a sec to get to it until tonight so here goes. The macro highlights changed cells which deals with the primary need - excellent! I can filter on Col L to get a summary of changes - if there was a line of code to do this at the end of the macro I could slot it in? It's strange that it writes changes to sheet2 for you and not for me but, hey, I can live with that! Note: the full end results are on view in the 2 latest test docs here which all in the forum can see if they so wish.

(P.S. in post#93 I gave you a bum steer ..... the macro ended so quickly I didn't think it had run! That line at the start put the entry in N1 which I'm not now getting)
______________________________<wbr>______________________________<wbr>________________________

todaydata (2 worksheets)

- sheet1 (1,000 rows)
- macro ends in N7 with 'COMPLETE' (N1 is blank)
- Highlighting works for changed cells; filter on Col L shows 62 rows have some change

- sheet2 has nothing new written (header row remains as was)
- macro ends on A40
- SUMPRODUCT(($A$1:$A$1000<>"")*<wbr>1)+1 counter in Q1 (=2)

yesterday (1 worksheet - 1,000 rows)

- 'zm129 - Sep17' is sheet name
- macro ends showing Cols AA:AL selected (fully populated & highlighting changed cells)
- Cols A to K have highlights where change detected (filter on Col L shows 62 rows have some change)
- SUMPRODUCT counters in Q1 (=1001), Z1 (=194), AQ1 (1001), BA1 (=194) - could these impact?

Questions:

(1) do sheet names matter or does the macro try to work on any 2 open workbooks?
(2) SUMPRODUCT entries - assume they don't matter to the flow of the macro?

I hope the above confirms everything you might be wondering about. For the absence of doubt I'm pasting the macro below (with delay now commented out):

Sub Macro4()
'
' Macro4 Macro
' below is macro as of Dec 20 12:35 pm

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

 

[1cyril1]

Roderick - what does your final sentence refer to .... some plugin or other?
Re. Inquire/Compare .... doest it look for a match on Col A only or where is it trying to match things?

 

[oldbrewer]

cyril - as we are there now - ish - ask a moderator if you can send me your spreadsheet - in the meantime I will look at the google sheets version.

What are you filtering on column L ?

Bob

 

[1cyril1]

Bob, I've mailed a Moderator as suggested - will let you know the outcome. Filtering on '1's in Col L pulls all of the changed rows together for viewing (these are what would appear in sheet2 if the macro finished out properly on my side)

 

[1cyril1]

Hi Bob, the moderator I mailed following your suggestion was Jon von der Heyden ..... I've no response to date. Wondering if he's the right person or is there a super moderator for such requests that I might try?

Did you get to look at the 2 docs I provided a link to recently? (you may need to click on an option top-right initially to view docs in chronological order)

Cheers!
Cyril