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

 

[oldbrewer]

Con Number (key)

Tr Code

C

Start Date

Fin Date

Approved Value

Used Value

Ven Numb

Ven Name

BU

REF

No of rows =

11

101

A

A

A

A

A

A

A

A

A

A

102

A

A

A

A

A

A

A

A

A

A

103

A

A

A

A

A

A

A

A

A

A

104

A

A

A

A

A

A

A

A

A

A

105

A

A

A

A

A

A

A

A

A

A

106

A

A

A

A

A

A

A

A

A

A

107

A

A

A

A

A

A

A

A

A

A

108

A

A

A

A

A

A

A

A

A

A

109

A

A

A

A

A

A

A

A

A

A

110

A

A

A

A

A

A

A

A

A

A

this is the yesterday spreadsheet

in Z1 enter this formula

SUMPRODUCT(($A$1:$A$1000<>"")*1)

in BA1 enter this formula

=SUMPRODUCT((AA1:AA1000<>"")*1)

in sheet 2 of the today spreadsheet

in cell Q1 enter this formula

=SUMPRODUCT(($A$1:$A$1000<>"")*1)+1

J

13

Z

11

these 2 cells show how

for each J value

Z loops from 2 to [in this case 11]

ie cell Z1 value

the delay code allows you to watch

in a reasonable time frame

<colgroup><col><col><col><col><col><col><col><col span="2"><col><col><col><col span="2"><col><col span="2"><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[1cyril1]

Apologies for delay in response, it's been a bit crazy with little time. Earlier today in my office (Windows environment) I applied your latest inputs (post#51) and then ran the macro in post#50. Unfortunately, Excel hung for a few minutes and I was forced to kill the process. I’ve retried on my Mac at home just now and it seemed to run but there’s no highlighting at all and no data added to Sheet2 (the test data is slightly different between work and home but only slightly).

I will try again in the office on Friday along with anything else you might suggest. For clarity can I recheck with you:

1. when you say Sheet2 do you mean the 2^nd worksheet regardless of name?
2. do you think using 2 macro-enabled workbooks matters (todaydata needs to be .xlsm to store/run macro from)?
3. did you try on my 2 workbooks (post#49)? to ensure we're on the same page?

 

[oldbrewer]

1) No - I mean the second SHEET in the today workbook.
2) Yesterday has to be macro enabled also, yes.
3) No I didn't - but I will do this morning (only 8 am here)

 

[oldbrewer]

I made my today and yesterday sheets identical to yours, added the auto row counters and made sure sheet2 of today was completely blank apart from the auto row counter. I then ran the macro from the today sheet and as far as I could see all highlighting was done correctly.

 

[oldbrewer]

....and the new table of "today rows that were in part highlighted" appeared correctly in sheet2 of today....

 

[1cyril1]

Thanks Bob! That's very interesting as it bombs out for me on that final sheet2 piece with the same error as in post#49 "Rows(Cells(1, 17)).Select". This happens after a very long time considering my sample has only 30 rows in each sheet being compared (approx. 10 mins). The good news is:


(a) the highlighting of matched/changed cells works
(b) a new entry in todaydata is also highlighted in todaydata
(c) the first changed record appears at the top of sheet2 in todaydata before the macro fails there

The entry that we had been getting of a '1' in Col L of todaydata is not happening in the first sheet (it is in sheet2 for the one record added there!). It would be good to have it in sheet1 also as I could filter for changes that way?

I will try again early next week across some real data - around 3,000 rows.

I just wanted to keep you posted and to report good progress with the core highlighting requirement. Enjoy your weekend!

 

[oldbrewer]

it is worrying that with identical workbooks it runs differently for you - have you got 3 automatic row counters, 2 in yesterday and 1 in today SHEET 2

 

[1cyril1]

This is quite addictive .... had a quick look again just now (Sunday) using the 2 workbooks here It worked as previously reported most of the way before crashing after about 10 mins. The outcome is saved in the workbooks concerned so best to check direct. You will see some more info on the error in the screenshots at same link. At least we should now be seeing the same story unfold so hope that's helpful. There are counters in I30 and I31 in 'yesterday' .... not sure if of interest. I have entered the formulae in Z1 and the 2 other cells as you advised - are these the counters you refer to?

I've been working off my Mac but don't expect that to be an issue. I'm keen to try on say a thousand rows (both workbooks) of real-life data asap at my work (Windows) office over coming days and will share the results here. Fingers crossed!

 

[oldbrewer]

the 2 counters in yesterday are to set the loop limats - cells(1,17) in today sheet 2 are for the today rows with a 1 at the end to be transferred in

if you have all those counters in the right place I do not see how the output can be different to mine

when it is totally sorted it will speed up by a factor of 100 million when we take the delay loop out

 

[oldbrewer]

it should not crash - what are the values of j and z when it crashes - they are there for diagnostic reasons