Thread: matching lists

I typically download a trial balance off a legacy mainframe. I use the trial balance to create financial reports. I am looking for suggestions to quickly compare lists that are a couple of days apart in terms of run date that may contain 500 rows or more of data. Typically the later download (List B) is essentially identical to List A , there is usually 10 or so additions or deletions to the A list. What is the best way to find the 10 changes. Changes can be either to a list A amount or a new posting to List B which will have a new fund, program, Account # and amount!

An example would typically be:

List A
Fund Program Account Amount
100 2010 50001 100.00
100 2020 50001 110.00
100 2030 50002 120.00
100 2040 50001 130.00
100 2050 50002 140.00
List B (has one new addition and one change to the amount )
Fund Program Account Amount
100 2010 50001 100.00
100 2020 50001 120.00
100 2060 50003 150.00
100 2030 50002 120.00
100 2040 50001 130.00
100 2050 50002 140.00
Thanks everyone! Happy New Year!

Hi,

One solution:

1. Put both lists in the same 'list' (see below)
2. F2: =IF(A2="A";-E2;E2) copy down
3. Make a Pivottable over the whole list (A1:F1000), with
- Row Fields: Fund,Program,Account
- Data Items: Diff
(and remove the subtotals to make it look better)

This will show the difference.

Regards,
Ola

A..............B............C..............D.............E..............F
List........Fund........Program.....Account.....Amount....Diff
A.............100........2020.........50001........110..........-110
...
B.............100........2020.........50001........120..........120
...

Never thought about Pivot Tables as a solution. Thanks! I need to get better at using them. The only other thought I had was to use the sort function color coding the original list one color and the other another and looking for matches that way, but it is rather cumbersome! Any other great thoughts out there?

Steve in beautiful downtown Los Gatos CA