Macro to reconcile to two different worksheets of data and determine differences

Julie4533 · Feb 12th 2013

Hello,

I am in need of assistance with creating a macro that will look at two tabs of raw data, populate all unique values in a new tab, pull in the values from each raw data tab into the new tab to create a consolidated comparison of both raw data tabs. I have attached an example of what I am working with. The color coated tabs are what I am hoping to be able to create from the raw data tabs using a macro.

Just want to mention that I know VERY little about creating macros and/or VBA - I always use something called ASAP Utlilities to help me with any short cuts when manipulating data within a spreadsheet. Currently I am using a SUMIF formula to pull in the data but I would prefer to use something to speed up the process. Any assistance or pointers would be greatly appreciated!

Julie

jindon · Feb 12th 2013

Re: Macro to reconcile to two different worksheets of data and determine differences

try

Code

Option Explicit




Sub test()
    Dim a, i As Long, ii As Long, w, e, n As Long, myList, y
    myList = VBA.Array("Company 1", "Company 2")
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each e In myList
            a = Sheets(e).Range("a3").CurrentRegion.Value
            For i = 2 To UBound(a, 1)
                If Not .exists(a(i, 3)) Then
                    ReDim w(1 To 6)
                    For ii = 1 To 3
                        w(ii) = a(i, ii)
                    Next
                    .Item(a(i, 3)) = w
                End If
                w = .Item(a(i, 3))
                w(IIf(e = myList(0), 4, 5)) = a(i, 4)
                .Item(a(i, 3)) = w
            Next
        Next
        ReDim a(1 To .Count + 1, 1 To 6)
        n = n + 1
        a(n, 4) = myList(0): a(n, 5) = myList(1): a(n, 6) = "Differences"
        For Each e In .keys
            w = .Item(e)
            n = n + 1
            For ii = 1 To UBound(.Item(e))
                a(n, ii) = .Item(e)(ii)
            Next
            If (.Item(e)(4) <> "") * (.Item(e)(5) <> "") Then .Remove e
        Next
        If .Count > 0 Then y = Application.Transpose(Application.Transpose(.items))
    End With
    With Sheets("reconciliation").Cells(1).Resize(n, 6)
        With .CurrentRegion
            .ClearContents
            .Borders.LineStyle = xlNone
        End With
        .Value = a
        .Columns(.Columns.Count).Resize(n - 1).Offset(1) = "=rc[-2]-rc[-1]"
        With .Rows(n + 1).Columns("d:f")
            .Formula = "=sum(r2c:r[-1]c)"
            .Borders(8).Weight = 2
        End With
    End With
    With Sheets("Differences").Cells(1).Resize(, 6)
        With .CurrentRegion
            .ClearContents
            .Borders.LineStyle = xlNone
        End With
        .Value = Array("", "", "", myList(0), myList(1), "Differences")
        With .Offset(1).Resize(UBound(y, 1))
            .Value = y
            .Columns(.Columns.Count).Formula = "=rc[-2]-rc[-1]"
        End With
        With .Parent.Rows(UBound(y, 1) + 2).Columns("d:f")
            .Formula = "=sum(r2c:r[-1]c)"
            .Borders(8).Weight = 2
        End With
        
    End With
End Sub

Display More

Julie4533 · Feb 12th 2013

Re: Macro to reconcile to two different worksheets of data and determine differences

OMG - this is almost exactly what I need. The only issue is that the total in the "reconciliation" tab and the "differences" tab.

jindon · Feb 12th 2013

Re: Macro to reconcile to two different worksheets of data and determine differences

No idea what you are talking about.

The figures are identical to your original calculation.

xl4betrm · Nov 20th 2014

Compare More Than Two Worksheets and Reconcile Differences

I need to compare more than two worksheets and reconcile differences for Charge amounts.
Each sheet corresponds to a unique Company, e.g. Company1, Company2, Company3, etc.
The data columns are the same in all sheets - Name, Product, ID Number and Charge.

It needs to produce a “Reconciliation” sheet with the comparison results from each of the multiple sheets.
Also, it needs to create a “Differences” sheet with only those having Charge amount differences.

I found some excellent VBA code written by Jindon here: http://www.ozgrid.com/forum/showthread.php?t=174992 and have modified it in my attempt to produce the above. I have attached my example which uses 4 Company worksheets. You will see it generates the Reconciliation and the Differences sheets, but results only show for Company1 and Company4. It does not list results of Company2 and Company3. Finally, there’s an issue with the results. “Allen GHI 111111” with a Charge amount of $250 is in the first three Company sheets, but not in Company4. However, in the results it shows $250 under Company4 when it should be blank.
If you can help me with this it will greatly be appreciated!

jindon · Nov 20th 2014

Re: Macro to reconcile to two different worksheets of data and determine differences

xl4betrm

Please open your own thread, asking question in someone else thread is prohibited.

Macro to reconcile to two different worksheets of data and determine differences

Participate now!

Share