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