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?

"Minuette" <Minuette@discussions.microsoft.com> wrote in message
news:87CA6665-92A0-42CF-A811-D72E1AC6A42D@microsoft.com...
>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

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" <Minuette@discussions.microsoft.com> wrote in message
> news:87CA6665-92A0-42CF-A811-D72E1AC6A42D@microsoft.com...
> >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
>
>
>

"Minuette" <Minuette@discussions.microsoft.com> wrote in message
news:4AA0D143-D4F0-4436-B3DF-17110478B2BF@microsoft.com...
> 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

It worked!! Brilliant - thanks Bruno

"Bruno Campanini" wrote:

> "Minuette" <Minuette@discussions.microsoft.com> wrote in message
> news:4AA0D143-D4F0-4436-B3DF-17110478B2BF@microsoft.com...
> > 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
>
>
>