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]

Latest view from my late night Mac tests:

Runs fine from highlighting perspective and '1's added ok in both spreadsheets. Alas, nothing has been written to sheet2 in todaydata
- Sheet1 - cursor finishes on N7 with COMPLETE entered there
- Sheet2 - cursor moved from A1 to land on A40 (!!)

yesterday
- macro ends with cols AA1 to AK1 selected (down to row 15,000) - some highlighted but all blank
- cell I30='16' and I31='27' ... is thise expected? (there are 27 rows of data excluding header row - is that the I31 element? no idea where '16' is from)

 

[oldbrewer]

the macro should be stopping before it even tries to copy to sheet 2 - what is in N1 of sheet 1 of today ?

16 and 27 are the J and Z values of the loops so you had 16 rows in today and 27 in yesterday

I am away now till next Thursday - have a good one !!!!

Bob

 

[1cyril1]

Bob, there is a blank in N1!! I can see in your code what should be appearing there ... "Cells(1, 14) = "starting to move rows to sheet 2": GoTo 999"

I have 16 rows in today and 28 in yesterday (incl header row) so one of the values is correct

That's it from me too for now. What can I say: you've been amazing, flying the flag, with great skill and patience, for the so-called older brigade (and I'm only a decade behind you). You deserve a huge Christmas present from me but for now I want to wish you a huge "Happy Christmas". Take care until the next time!

Cyril

 

[oldbrewer]

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)

 

[Roderick_E]

rodericke.com/xlsuper use the Sheet Comparer feature

 

[oldbrewer]

roderick - thanks for coming in to assist - never heard of it but will investigate - is sheet comparer ok with Excel 2000 ?

 

[Roderick_E]

roderick - thanks for coming in to assist - never heard of it but will investigate - is sheet comparer ok with Excel 2000 ?

The file is an xlsb file so it will require 2007 or higher

 

[oldbrewer]

thanks - out of interest could it cope with 10 rows in one spreadsheet and 25 rows in the other ?

 

[Roderick_E]

thanks - out of interest could it cope with 10 rows in one spreadsheet and 25 rows in the other ?

It's designed to handle tens of thousands of rows especially if each sheet has a UID, unique identifier it can cross reference.

 

[oldbrewer]

I meant different numbers of rows in each sheet