Compare worksheets from two workbooks

[michaelg1040]

I'm hoping someone can help me with this problem that has been driving me insane! I have a master tracking document that I use to record project information. My client sends me an updated schedule each week which may (or may not) have additional stores on it and some of the details of the stores may have changed. I need a macro to capture these changes from the source spreadsheet (the one the client sends) and update the master tracker. The master tracker has a lot of additional columns of data that I add in myself about each project so I don't want to lose this information. The macro needs to see if the store on the source sheet is already on the master tracker and if it is then it needs to check to see if any of the columns below have changed. If the store isn't on the master tracker then it needs to be added. There are around 750 stores on the master tracker at the moment so to do it manually takes forever!

I hope you can help!

Master Spreadsheet

Column A - Retail Region
Column B - Project Name
Column C - Postcode
Column D - Net Selling Area
Column E - Project Manager
Column F - Contractor
Column I - Start On Site
Column J - Launch Date


Source Spreadsheet

Column C - Retail Region
Column D - Project Name
Column I - Postcode
Column J - Net Selling Area
Column M - Project Manager
Column N - Contractor
Column P - Start On Site
Column Q - Launch Date

 

[Andrew Poulsom]

I tested the code before I posted it.

The original data in A1:B5 on Sheet1 in workbook Master was:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=64 height=51>Retail Region</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Project Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>d</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>e</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>f</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>g</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj6</TD></TR></TBODY></TABLE>

The data in C1:D5 on Sheet1 of workbook Source was:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=64 height=51>Retail Region</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Project Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>DD</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>EE</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FF</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Blah</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj5</TD></TR></TBODY></TABLE>

After running the macro the data in A1:B5 on Sheet1 in workbook Master was:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=64 height=51>Retail Region</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Project Name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>DD</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>FF</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>EE</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Blah</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Proj5</TD></TR></TBODY></TABLE>

 

[michaelg1040]

I'm at a loss then! I can't understand why, if it's finding a match between the 2 workbooks it is then deleting everything. Would it help if I sent you the 2 workbooks to see if there are any anomalies that I've missed?

 

[chuf]

Andrew,

I've been able to adapt this code for a matching exercise a colleague needs to undertake .. there's just one issue i hope you can help with please..

in our data we may have more than one reference in the matching column in the master worksheet.. this code appears to input the data from the next unmatched against that reference.

I've tried adapting the code to allow for more than one but have been unable to.. is it possible with this code, or would it need a complete re-write .

sorry to butt in on this thread.. but its a fab code. Chuf

 

[Andrew Poulsom]

Andrew,

I've been able to adapt this code for a matching exercise a colleague needs to undertake .. there's just one issue i hope you can help with please..

in our data we may have more than one reference in the matching column in the master worksheet.. this code appears to input the data from the next unmatched against that reference.

I've tried adapting the code to allow for more than one but have been unable to.. is it possible with this code, or would it need a complete re-write .

sorry to butt in on this thread.. but its a fab code. Chuf

The code assumes there is only one entry for each item.

 

[michaelg1040]

Has anyone made any further progress with this? The 'delete' part of the code isn't working.

 

[Andrew Poulsom]

I don't know what else I can do. I have demonstrated that it is working. Something must be different between your setup and mine.

Why don't you put a break point in your code and step through it to see what's happening?

 

[michaelg1040]

Andrew,
Thanks for all your help. I've managed to get it resolved now - the source file sent through by my client this week had slightly changed so the wrong columns were being looked at.

 

[mavenusa]

The code supplied by Andrew Poulsom works great I have been using it. I need one variation to it. Can someone please provide the tested code changes to be able to have the macro do the compare of two 'closed' workbooks. In the example of the macro provided by Andrew Poulsom (which works great) both workbooks must be open for it to work.

Here is the code provided back in Jan. 2010:

=========================================================
Sub Test()
Dim wbSource As Workbook
Dim wsSource As Worksheet
Dim Rng As Range
Dim wbMaster As Workbook
Dim wsMaster As Worksheet
Dim Cell As Range
Dim Target As Range
Dim r As Long
Set wbSource = Workbooks("Source.xls")
Set wsSource = wbSource.Worksheets("Sheet1")
With wsSource
Set Rng = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
End With
Set wbMaster = Workbooks("Master.xls")
Set wsMaster = wbMaster.Worksheets("Sheet1")
For Each Cell In Rng
With wsMaster
With .Columns("B")
Set Target = Nothing
Set Target = .Find(What:=Cell.Value, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
End With
If Not Target Is Nothing Then
r = Target.Row
Else
r = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
End If
.Range("A" & r).Value = Cell.EntireRow.Range("C" & 1).Value
.Range("B" & r).Value = Cell.EntireRow.Range("D" & 1).Value
.Range("C" & r).Value = Cell.EntireRow.Range("I" & 1).Value
.Range("D" & r).Value = Cell.EntireRow.Range("J" & 1).Value
.Range("E" & r).Value = Cell.EntireRow.Range("M" & 1).Value
.Range("F" & r).Value = Cell.EntireRow.Range("N" & 1).Value
.Range("I" & r).Value = Cell.EntireRow.Range("P" & 1).Value
.Range("J" & r).Value = Cell.EntireRow.Range("Q" & 1).Value
End With
Next Cell
End Sub
=========================================================

 

[kaushikharsh]

Macro to compare complete data set from different workbooks and insert unmatched records in separate work book

Hi Andrew,

I want to create a macro which can:

1) Compares complete data set of two different workbooks.
2) Inserts the unmatched dataset (Records) in separate workbook.

Pls guide me thru this...
Thanks in advance!!​