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]

post 17 the link does not open - says I need a microsoft account - can you put it on google sheets

if the 2 spreadsheets layout is identical, easy to find differences - do you want them highlighted on one or both spreadsheets ?

 

[1cyril1]

post 17 the link does not open - says I need a microsoft account - can you put it on google sheets

if the 2 spreadsheets layout is identical, easy to find differences - do you want them highlighted on one or both spreadsheets ?

Apologies .... I thought I'd got that link amended as per Post#18 "I've edited the post as the original link may not work outside of my org after all - it's now amended above but you can also try this (Download-Sept2017):wink:" - please try again? Yeah, it would be good to see the changes highlighted in both. Then on to Step2 ... "all changed rows in the most recent file to be copied to a new 'Changes' worksheet in that file"

 

[oldbrewer]

I am getting confused - I thought we were working on 2 spreadsheets with the same column headers in the same order and we were comparing every column...

 

[1cyril1]

I am getting confused - I thought we were working on 2 spreadsheets with the same column headers in the same order and we were comparing every column...

Bob, we are - Post#17 still applies (amended for link in #18 "same layout/format as shown here").

Life is complicated these days .... our security folk have blocked Google Drive so I can't check the linked doc from the office now - it had better align with above (will check from home later). Let me know if there is still any uncertainty from your side?

 

[oldbrewer]

idnum	color	fruit					idnum	color	fruit
id1	red	apple					id1	red	apple
id2	yellow	banana					id7	red	apple
id3	green	plum					id8	BLACK	AUBERGINE
id4	red	apple					id9	green	plum
id5	yellow	banana					id6	green	plum
id6	green	plum					id10	red	apple
id7	red	apple					id2	yellow	banana
id8	yellow	banana					id3	BLACK	AUBERGINE
id9	green	plum					id4	red	apple
id10	red	apple					id5	yellow	banana
			WILL there always be the same number of rows
			MIGHT the right hand spreadsheet be in a different order

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

 

[1cyril1]

a) No, the number of rows is variable in each workbook
b) Both docs have the "same layout/format as shown here" - we're talking about a download file that's being compared to the previous download file i.e. same layout, just some records will be different as at diff points in time

Please use the "the Download-Sep2017 doc" I supplied at the Google link here as that's based on the actual layout & data types involved
(if you can't access I'd be surprised but please let me know; I'd paste here only I can't - on a Mac now at home and didn't work out previously; in the office I'm on Windows but download of recommended utility not permitted ... how can you win!!)

#believe

 

[1cyril1]

a) No, the number of rows is variable in each workbook
b) Both docs have the "same layout/format as shown here" - we're talking about a download file that's being compared to the previous download file i.e. same layout, just some records will be different as at diff points in time

Please use the "the Download-Sep2017 doc" I supplied at the Google link here as that's based on the actual layout & data types involved
(if you can't access I'd be surprised but please let me know; I'd paste here only I can't - on a Mac now at home and didn't work out previously; in the office I'm on Windows but download of recommended utility not permitted ... how can you win!!)

#believe

I didn't deal with your 'order' query: if being in the same order helps we can Sort both docs on the key field (Col-A). Please confirm what will work best with proposed solution.

 

[oldbrewer]

Order does not matter. But keyfield is in different columns. So they don't have the same layout. If the download file is being compared to the previous download file then surely only the number of rows will vary. If you copy the download file you can paste it in the reply box - BUT_ before you do put borders around every cell.

 

[1cyril1]

Order does not matter. But keyfield is in different columns. So they don't have the same layout. If the download file is being compared to the previous download file then surely only the number of rows will vary. If you copy the download file you can paste it in the reply box - BUT_ before you do put borders around every cell.

Keyfield is Col-A in both i.e. not in different cols, not sure where that came from? And yes, you're correct regarding number of rows can and will vary as stated. If Post#27 is at odds with any of that I need to know. Hope that clarifies.

 

[oldbrewer]

going out now, will look at it in the morning.....