Split Commissions - Pivot Table

Hey everyone,

I have what seems to be a very complex problem.

We have reports that contain all sales by our sales reps, as well as the commission they earn on these sales. Certain product lines however, are split between the reps, but are located in a single reps territory.

For example, Matt sells all MMM product lines, but all commissions should be split 1/3 to Scott, and 2/3 to Matt. This would mean Scott's commissions should be higher by 1/3 of the MMM commissions, and Matt's commissions should be lower by 1/3 of the MMM commissions, since the sales were made in his territory.

I need to be able to show this in a pivot table, so the total commissions shown in that table reflect the splits as well, without manually separating the different product lines, as we have 20,000 + Lines each month.

See dummy file attached.

Any help in this would be greatly appreciated.

Bumping up post

Bumping again

Guessing noone really knows :P

Hoping someone knows a way

Never going to give you up...