VBA code to sort, compare, and line up two lists of data.

[mrzesty973]

I need vba code that can take a list of invoices that are found on sheet 1 (called AR Invoices) and sheet 2 (called AP Invoices). The code would perform the following operations and then place the results on a third sheet called Recon Sheet. The lists would be sorted in ascending order, and then compared to each other. When there is a match, the code will line up the data onto one row. If there is not a match, the code will insert the word "missing." There is also the potential for duplicates. The duplicates would get separated and moved to separate columns. The final result would look like the screen shot below.

List A			List B			Duplicates
AR Invoices	Total		AP Invoices	Total		Invoice #	Total
14221	$ 1.00		14221	$ 2.00		3044776	$ 6.00
3044771	$ 2.00		Missing			7400362	$ 5.00
3044773	$ 3.00		3044773	$ 3.00
7384580	$ 4.00		Missing
7400362	$ 5.00		7400362	$ 4.00
Missing			3044775	$ 5.00
Missing			3044776	$ 6.00
7400362	$ 5.00		3044776	$ 6.00

I have included a workbook with the data. The lists will vary in length from project to project.

Thank you in advance.

[rlv]

Not exactly what you want, but this should get you started.

Sub DoStuff()
    Dim rngTmp As Range
    Dim rngCells As Range
    Dim wsRecon As Worksheet


    Set wsRecon = Worksheets("Recon")
    wsRecon.Cells.Clear


    wsRecon.Range("A1").Value = "Invoice #"
    wsRecon.Range("B1").Value = "AR Invoices"
    wsRecon.Range("C1").Value = "AP Invoices"


    Worksheets("AR Invoices").UsedRange.Copy wsRecon.Range("A1").Offset(1, 0)
    Worksheets("AP Invoices").UsedRange.Copy wsRecon.Range("A1").Offset(wsRecon.UsedRange.Rows.Count, 0)


    wsRecon.UsedRange.RemoveDuplicates Columns:=Array(1), Header:=xlYes


    Set rngCells = Application.Intersect(wsRecon.UsedRange, wsRecon.Range("A1").EntireColumn)
    Set rngCells = rngCells.Offset(1, 0).Resize(rngCells.Rows.Count - 1, rngCells.Columns.Count)


    For Each rngTmp In rngCells
        rngTmp.Offset(0, 1).Value = Application.VLookup(rngTmp.Value, Worksheets("AR Invoices").UsedRange, 2, False)
        If VBA.IsError(rngTmp.Offset(0, 1).Value) Then
            rngTmp.Offset(0, 1).Value = "Missing"
        End If


        rngTmp.Offset(0, 2).Value = Application.VLookup(rngTmp.Value, Worksheets("AP Invoices").UsedRange, 2, False)
        If VBA.IsError(rngTmp.Offset(0, 2).Value) Then
            rngTmp.Offset(0, 2).Value = "Missing"
        End If


        If rngTmp.Offset(0, 1).Value = rngTmp.Offset(0, 2).Value Then
            rngTmp.Offset(0, 3).Value = "Duplicate!"
        End If
    Next rngTmp
    wsRecon.UsedRange.NumberFormat = "$#,##0.00"
    rngCells.NumberFormat = "0"
End Sub