Pivot Tables from 2 Data tables (with a unique key)

[x-amount]

I have 2 tables, Sales and Claims (for an insurance company).

The Sales table has Policy Number in the first column. As a Sale is renewed, it keeps the same policy number, hence this can be duplicated.

The Claims table has Policy Number in the first column. One Policy can have many claims.

I am looking to create a pivot table listing all of the unique Sales Policy Numbers and various summed calculations that lie within the Policy table, PLUS (and here's the tricky bit!) I want it to include the sum of Claims per Policy Number.

Is this possible? I've searched high and low, though the lack of answer for such a fundamental question is either very hidden, or I'm just not explaining it in the right terms!

I have 3 alternative options that all have their drawbacks:

Do it in SQL (the source is originally an SQL DB) - though I'm sadly not particularly SQL proficient and it requires a number of functions that are not available in SQL

Advanced filter and various Sumifs (trying to move away from formulas tbh for speed. Woudl like this executable in a few minutes)

Create a giant table, with the Sales table, then paste the claims table below it and to the right, though put the Policy Number in the first column, then Create a PT on the giant table as theoretically the sums will all add up.

As the data for Sales runs at about 300k the giant table may be a step too far in abusing excel resources

Any thoughts?!

 

[NiceGuyRitchie]

Since this is a database task I would run it from the database in SQL, a database is designed to perform this type of operation quickly and efficiently. If that is not practical than you can kind of make an excel database here.

Your idea of combining the two tables into one single mega-table worksheet sounds like the most doable option. I've attempted to get a pivot table to reference two sheets but it wont do it. Once the data is combined, point the pivot table to your mega-table sheet and run it that way.

I would stay away from array or sumproduct type of formulas here because with +300K records you could blow up excel. It will just bog down and hang there. I worked with a guy who used to let his spreadsheet arrays half a million records for like a half an hour while he would go and get coffee.

 

[x-amount]

Pretty much as I thought, cheers.