Compare worksheets and generate list of missing data?
 

I need to compare a new worksheet to a very large master worksheet and
generate a list of names which are missing from the master worksheet. Is
there a function that will help me do this?

Compare worksheets and generate list of missing data?
 

"Minuette" wrote in message
...
I need to compare a new worksheet to a very large master worksheet and
generate a list of names which are missing from the master worksheet. Is
there a function that will help me do this?

Here is one, supply your ranges in Definitions (initial cells only):

=========================
Sub ComparingData()
Dim CompareColl As New Collection
Dim MissingFromMaster As New Collection
Dim SourceRange As Range
Dim CompareToRange As Range
Dim TargetRange As Range, i

' Definitions
' --------------------------------------------------
Set SourceRange = [Sheet10!W70]
Set CompareToRange = [NameSheet!I32]
Set TargetRange = [Sheet10!X70]
'---------------------------------------------------

Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
Set CompareToRange = Range(CompareToRange, CompareToRange.End(xlDown))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each i In SourceRange
On Error Resume Next
CompareColl.Add i, i
Next
For Each i In CompareToRange
On Error GoTo MissingName
CompareColl.Add i, i
On Error Resume Next
MissingFromMaster.Add i, i
Continue:
Next
On Error GoTo 0

For i = 1 To MissingFromMaster.Count
TargetRange(i) = MissingFromMaster(i)
Next

Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

MissingName:
Resume Continue

End Sub
===========================

Let me know how things go.
Ciao, Bruno

Compare worksheets and generate list of missing data?
 

Hi Bruno,

Thanks for the info. I have never used Visual basic before so have copied
and pasted your work and then changed the data ranges.

Unable to run it - keep getting 'Syntax error' on Sub ComparingData () line.

It's obviously beyond me but thanks so much for trying.



"Bruno Campanini" wrote:

"Minuette" wrote in message
...
I need to compare a new worksheet to a very large master worksheet and
generate a list of names which are missing from the master worksheet. Is
there a function that will help me do this?

Here is one, supply your ranges in Definitions (initial cells only):

=========================
Sub ComparingData()
Dim CompareColl As New Collection
Dim MissingFromMaster As New Collection
Dim SourceRange As Range
Dim CompareToRange As Range
Dim TargetRange As Range, i

' Definitions
' --------------------------------------------------
Set SourceRange = [Sheet10!W70]
Set CompareToRange = [NameSheet!I32]
Set TargetRange = [Sheet10!X70]
'---------------------------------------------------

Set SourceRange = Range(SourceRange, SourceRange.End(xlDown))
Set CompareToRange = Range(CompareToRange, CompareToRange.End(xlDown))
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each i In SourceRange
On Error Resume Next
CompareColl.Add i, i
Next
For Each i In CompareToRange
On Error GoTo MissingName
CompareColl.Add i, i
On Error Resume Next
MissingFromMaster.Add i, i
Continue:
Next
On Error GoTo 0

For i = 1 To MissingFromMaster.Count
TargetRange(i) = MissingFromMaster(i)
Next

Exit_Sub:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Exit Sub

MissingName:
Resume Continue

End Sub
===========================

Let me know how things go.
Ciao, Bruno

Compare worksheets and generate list of missing data?
 

"Minuette" wrote in message
...
Hi Bruno,

Thanks for the info. I have never used Visual basic before so have copied
and pasted your work and then changed the data ranges.

Unable to run it - keep getting 'Syntax error' on Sub ComparingData ()
line.

It's obviously beyond me but thanks so much for trying.

How did you try to run the procedure?
You should run it by clicking the Button you've previously
assigned the macro to, or execute ComparingData from
the immediate window.

Ciao
Bruno

Compare worksheets and generate list of missing data?
 

It worked!! Brilliant - thanks Bruno

"Bruno Campanini" wrote:

"Minuette" wrote in message
...
Hi Bruno,

Thanks for the info. I have never used Visual basic before so have copied
and pasted your work and then changed the data ranges.

Unable to run it - keep getting 'Syntax error' on Sub ComparingData ()
line.

It's obviously beyond me but thanks so much for trying.

How did you try to run the procedure?
You should run it by clicking the Button you've previously
assigned the macro to, or execute ComparingData from
the immediate window.

Ciao
Bruno