Compare 2 spreadsheets and highlight differences

1cyril1

Board Regular
Joined
Mar 31, 2003
Messages
89
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
 
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 ?
 
Upvote 0

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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"
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
[TABLE="width: 727"]
<colgroup><col span="9"><col><col></colgroup><tbody>[TR]
[TD]idnum[/TD]
[TD]color[/TD]
[TD]fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]idnum[/TD]
[TD]color[/TD]
[TD]fruit[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id1[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id1[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id2[/TD]
[TD]yellow[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id7[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id3[/TD]
[TD]green[/TD]
[TD]plum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id8[/TD]
[TD]BLACK[/TD]
[TD]AUBERGINE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id4[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id9[/TD]
[TD]green[/TD]
[TD]plum[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id5[/TD]
[TD]yellow[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id6[/TD]
[TD]green[/TD]
[TD]plum[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id6[/TD]
[TD]green[/TD]
[TD]plum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id10[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id7[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id2[/TD]
[TD]yellow[/TD]
[TD]banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id8[/TD]
[TD]yellow[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id3[/TD]
[TD]BLACK[/TD]
[TD]AUBERGINE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id9[/TD]
[TD]green[/TD]
[TD]plum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id4[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]id10[/TD]
[TD]red[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]id5[/TD]
[TD]yellow[/TD]
[TD]banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]WILL there always be the same number of rows[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 6"]MIGHT the right hand spreadsheet be in a different order[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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:smile:
 
Last edited:
Upvote 0
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:smile:

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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top