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