1
votes

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)

1
how are the tables generated, from a query? will you have to perform this "merge" on a regular basis?WhiteHat

1 Answers

1
votes

Use multiple consolidation ranges (e.g. further details here - but you can stop short of creating the Table).

Ensure your separate sources have the same column labels:

SO34292124 example

N.B. 25+15 = 40 :)