+ Reply to Thread
Results 1 to 5 of 5

Compare worksheets and generate list of missing data?

  1. #1
    Minuette
    Guest

    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?

  2. #2
    Bruno Campanini
    Guest

    Re: Compare worksheets and generate list of missing data?

    "Minuette" <[email protected]> wrote in message
    news:[email protected]...
    >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



  3. #3
    Minuette
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > >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
    >
    >
    >


  4. #4
    Bruno Campanini
    Guest

    Re: Compare worksheets and generate list of missing data?

    "Minuette" <[email protected]> wrote in message
    news:[email protected]...
    > 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



  5. #5
    Minuette
    Guest

    Re: Compare worksheets and generate list of missing data?

    It worked!! Brilliant - thanks Bruno

    "Bruno Campanini" wrote:

    > "Minuette" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1