Create VBA return that will return customised results when comparing two worksheets

Trout58 · Jul 29th 2017

Hi All

Have spent a lot of time on the Web trying various formula’s and VB to compare two worksheets in the same workbook and produce results in the way I would ideally like but given my average level of skill and knowledge I haven’t had any success. I’ve posted on a couple of forums with a couple of replies which I greatly appreciate but haven’t the skill to make those work either. I know exactly what I want; maybe it can’t be achieved in Excel 2016, hopefully someone can assist.

I can run and export into Excel a membership list from a third party Oracle database as frequently as I like. These reports comprise 15 columns with one row per member and up to 25,000 rows (members).
1st data cell is B5 – end data cell is P25000

As a rule, there is always a difference between each list as to the number of members on each list, for various reasons. However, it’s simply not practical to try and manually find and isolate the differences; that is, who has been dropped off (or been added) to the membership list between each run, so I am hoping someone can tell me if it is feasible for VBA to do what I want and give me the code?

I have attached a sample workbook containing 50 records and representing the results I’m after.

The workbook has four (4) sheets: 1_Previous, 2_Current, 3_Dropped, 4_New.

Presumably by clicking a Command button the code will do the following:
a. Compare 1_Previous with 2_Current;
b. For every record that appears in 1_Previous but does not appear in 2_Current;
c. Place a COPY of those records in 3_Dropped in alphabetical order by member Surname;
d. THEN, for every record that doesn’t appear in 1_Previous but does appear in 2_Current;
e. Place a COPY of those records in 4_New in alphabetical order by member Surname.

Hope this can be achieved and thank you in advance for anyone who knows how.

Cheers
forum.ozgrid.com/index.php?attachment/73278/

MickG · Jul 30th 2017

Re: Create VBA return that will return customised results when comparing two workshee

Try this:-

Code

Sub MG30Jul56
Dim Rng As Range, Dn As Range, n As Long, txt As String, Ray() As Variant, Ac As Long
Dim Current As Variant, Previous As Variant, c As Long, r As Long, Var1 As Variant, Var2 As Variant
Current = Sheets("2_Current").Cells(5, 2).CurrentRegion
Previous = Sheets("1_Previous").Cells(5, 2).CurrentRegion
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
For r = 1 To 2
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
    If r = 1 Then
        Var1 = Current: Var2 = Previous
    Else
        Var1 = Previous: Var2 = Current
    End If
    For n = 5 To UBound(Var1, 1)
           ' This is for entire row if required
           ' Join(Application.Index(Var1, n, 0), ",")
            txt = Var1(n, 1) & Var1(n, 2) & Var1(n, 3)
         .Item(txt) = Empty
    Next n
    For n = 5 To UBound(Var2, 1)
        ' This is for entire row if required
        'txt = Join(Application.Index(Var2, n, 0), ",")
        txt = Var2(n, 1) & Var2(n, 2) & Var2(n, 3)
            If Not .exists(txt) Then
                c = c + 1
                ReDim Preserve Ray(1 To UBound(Var2, 2), 1 To c)
                For Ac = 1 To UBound(Var2, 2)
                    Ray(Ac, c) = Var2(n, Ac)
                Next Ac
            End If
    Next n
If r = 1 Then
With Sheets("3_Dropped").Range("B5").Resize(c, UBound(Previous, 2))
   .Parent.Range("B5").Resize(200, 100).ClearContents
   .Value = Application.Transpose(Ray)
   .Sort key1:=.Parent.Range("C5"), order1:=xlAscending, Header:=xlNo
End With
Erase Ray: c = 0
Else
With Sheets("4_new").Range("B5").Resize(c, UBound(Previous, 2))
   .Parent.Range("B5").Resize(200, 100).ClearContents
   .Value = Application.Transpose(Ray)
   .Sort key1:=.Parent.Range("C5"), order1:=xlAscending, Header:=xlNo
End With
End If
End With
Next r
End Sub

Display More

Regards Mick

Trout58 · Jul 31st 2017

Re: Create VBA return that will return customised results when comparing two workshee

Hi MickG
Thank you for this I'm confident it will work but I'm real basic at this - how do I get it to work? Do I have to put it on a particular sheet? I created a Command Button and inserted there but it didn't work - came up with a syntax error?

Cheers

MickG · Jul 31st 2017

Re: Create VBA return that will return customised results when comparing two workshee

Try the method below .
NB:- The first line of the code Had a Typo and a code error, but I have now altered it. So you can now copy from the thread to get the correct code.

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheets "3_Dropped" and "4_New" should now be updated.
Regrds Mick

Trout58 · Aug 1st 2017

Re: Create VBA return that will return customised results when comparing two workshee

Hi MickG

Brilliant thank you so much for all your help the code works exactly as I had hoped and highlights what i suspected all along i.e. the third parties database we run these reports from clearly has some major issues because we are getting variances in reports that run a week apart of over over 3,000 records. Thanks again mate you're a life saver. Also clearly pointed out really crap I am at VB and a lot of higher end formula lol; never mind i just don't get the need to do it very often that's why guys like you are a godsend!!!

Cheers
Brett

MickG · Aug 1st 2017

Re: Create VBA return that will return customised results when comparing two workshee

You're welcome

Create VBA return that will return customised results when comparing two worksheets

Participate now!

Share

Users Viewing This Thread