Reconciliation Query Excel 2007

**jcutler0902** · 01-03-2012, 04:53 PM

Hello -

To give a little back ground. I am pulling data from 3 seperate reports. (Sol 1, Sol 2, Sol 3 see attached). My goal is to make sure the order # and dollor amount match in Sol 1 & Sol 2 and the order number shows up in Sol 3). I can easily import each one into Excel.

Sol 1 - Shows the Order # but spilts out the order into seperate lines (added together will equal Sol 2, if there are multiple lines on an order)
Sol 2- Shows the Order # and the total amount of the order (That will equal Sol 1).
Sol 3 - Shows the Order #, Commision Amt, and when the order was placed.

I am trying to compare each report side by side to make sure it flows from one system to another and the final report (sol 3) showing the commission amount. We have been running into problems where data from Sol 1 (where the order is created & originates) does not show up on Sol 2 & Sol 3. The IT folks are looking to fix this, but I need to find a better way to show where orders are missing. Right now I am creating the attached and manually looking across the rows to make sure Order # xxx shows up in all 3 columns (and of course the dollar amount matches in Sol1 & Sol 2). Verifying the commision amount will be a seperate task.

I am just not sure what the best route to take on this.

Appreciate any input.

Jason

**arlu1201** · 01-04-2012, 04:15 AM

Do you receive Sol 1 and Sol 2 as 2 different excel workbooks? I can provide you a code that will compare these 2. You just have to tell me how you receive it.

**jcutler0902** · 01-04-2012, 05:09 AM

Good morning,

Yes all three reports are seperate workbooks. What you are looking at is the manual cutting and pasting into one workbook. Does your code allow for the 3rd report as well?.

Thanks

**arlu1201** · 01-04-2012, 05:23 AM

I will look into Sol 1 and Sol 2 right now and give you a comparison of the data. Then we can look at Sol 3. Does Sol 3 have anything to do with Sol 1 and 2? Is the commission calculated as a % of the dollar amount?

**jcutler0902** · 01-04-2012, 01:23 PM

Thank you so much for your help.

Looking forward to seeing what you come up with.

Arlette - Did by chance have anytime to review the above? Thanks again

**arlu1201** · 03-05-2012, 08:16 AM

As per your PM, i have compared Sol1 & Sol2 using this code. Ensure that Sheet1 & Sheet2 of the workbook containing this macro is blank for the macro to run properly.

Option Explicit
Dim FName As Variant
Dim lrow As Long
Dim i As Long

Sub reconcile_data()

FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 1st file")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.Name
End If

lrow = Workbooks(FName).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Workbooks(FName).Worksheets(1).Range("A1:C" & lrow).Copy ThisWorkbook.Worksheets(1).Range("A1")
Workbooks(FName).Close

FName = Application.GetOpenFilename(FileFilter:="All files (*.*), *.*", Title:="Please open the 2nd file")
If FName = "False" Then
    MsgBox "You have not selected a file."
    Exit Sub
Else
    Workbooks.Open Filename:=FName
    FName = ActiveWorkbook.Name
End If

lrow = Workbooks(FName).Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Workbooks(FName).Worksheets(1).Range("A1:C" & lrow).Copy ThisWorkbook.Worksheets(1).Range("E1")
Workbooks(FName).Close

With Worksheets(2)
    Worksheets(1).Columns("A:A").Copy .Range("A1")
    Worksheets(1).Columns("C:C").Copy .Range("C1")
    .Columns("A:C").RemoveDuplicates Columns:=1, Header:=xlYes
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("D2:D" & lrow).Value = "Sol1"
    .Range("B2:B" & lrow).FormulaR1C1 = "=SUMIF(Sheet1!C[-1]:C,Sheet2!RC[-1],Sheet1!C)"
    .Range("B2:B" & lrow).Copy
    .Range("B2").PasteSpecial Paste:=xlPasteValues
    
    Worksheets(1).Columns("E:E").Copy .Range("E1")
    Worksheets(1).Columns("G:G").Copy .Range("G1")
    .Columns("E:G").RemoveDuplicates Columns:=1, Header:=xlYes
    lrow = .Range("E" & .Rows.Count).End(xlUp).Row
    .Range("H2:H" & lrow).Value = "Sol2"
    .Range("F2:F" & lrow).FormulaR1C1 = "=SUMIF(Sheet1!C[-1]:C,Sheet2!RC[-1],Sheet1!C)"
    .Range("F2:F" & lrow).Copy
    .Range("F2").PasteSpecial Paste:=xlPasteValues
    
    .Range("E2:H" & lrow).Copy .Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    .Columns("E:H").Delete
    
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(2).Sort
        .SetRange Range("A:D")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("A" & i).Value = .Range("A" & i + 1).Value Then
            If .Range("D" & i).Value <> .Range("D" & i + 1).Value Then
                If .Range("B" & i).Value = .Range("B" & i + 1).Value Then
                    .Range("E" & i & ":E" & i + 1).Value = "Matched"
                Else
                    .Range("E" & i & ":E" & i + 1).Value = "Not Matched"
                End If
            Else
                .Range("E" & i & ":E" & i + 1).Value = "Not Matched"
            End If
        Else
            .Range("E" & i & ":E" & i + 1).Value = "Not Matched"
        End If
        i = i + 1
    Next i
End With

End Sub

After you review this, we can discuss how to incorporate Sol3 into this.