Slightly awkward requirements, so I apologise if the explanation isn't overly clear.
I have two tables, with very similar data (though not identical), which I'd like to merge together and total up as follows.
Both Tables Contain the following headings Invoice, Date, Account, No., Description, Blank, Credit, Debit, Total
However, they are for slightly different things (support and commission to be exact). Both tables contain multiple rows of data for various customers, but some customers may only be in one table or the other.
I've used pivot tables for each table individually to show the sum totals for each customer (so I have a table of every customers total support value, and a separate table for every customers total commission). Similarly to above though, customers may be in one pivot table but not the other.
What I would like is a single table to show every customer from both tables (if they are in both tables, I only want one record), with the total support (showing 0 if the customer isn't in the table), the total commission (again, 0 if the customer isn't is that table), and ideally the total overall (although this is a simple sum of the other two, so can be added in after if required...
As an example, if the relevant columns in two tables were;
Support Commission
Account | Total Account | Total
----------------- -----------------
A | 25.00 A | 5.00
A | 25.00 C | -10.00
A | 45.00 C | 10.00
B | 10.00 C | 30.00
B | -5.00 C | 25.00
C | 5.00 D | 25.00
C | 10.00 D | -5.00
C | 10.00 E | 15.00
E | 25.00
I'm trying to end up with a table that looks like;
Account | Support Total | Commission Total | Overall Total
----------------------------------------------------------------
A | 95.00 | 5.00 | 100.00
B | 5.00 | 0.00 | 5.00
C | 25.00 | 55.00 | 80.00
D | 0.00 | 20.00 | 20.00
E | 25.00 | 15.00 | 50.00
This isn't something I'd want to do manually, as my actual tables have 2000+ rows in them.
Any help would be greatly appreciated. (I've been messing around with various Excel features for a long time now and I've run out of ideas)