VBA code to allocate estimate revenues based on actual data

Hi Excel professionals,

I wanted to automate my manual allocation of estimated revenues and can't seem to think of a way except to create a VBA which I don't have enough excel knowledge to do so. Please see attached worksheet, col "A" to "E" is my actual data. My esti revenus is from cell "H2 to J5" and my expected allocation results are from cell "H10 to L21".

My current process is to create a pivot table and calculate how much each Agent's % based on total Company, State and Business code and apply that to the estimate revenue. This gets extremely long when the real actual data is over 900 rows.

Note: I use fake data to generate this example so the actual data heading is not the same as the example I attached (for example: "Agent" is in the example, but my actual work has it as "producer")

Please let me know if you can help.

Thanks,
yayayken
Allocation of estimated sales with 4 variables.xlsx

Will it be too much trouble if I add another variable? Please see attached. I included a product column so the spreading will need to increase another variable. Please let me know if you can help. Thank you so much.

Yes this is great. I have one more scenario cause sometimes my department heads give me the products in the estimate so I can narrow down to spreading on those products only. Please see v4 worksheet and let me know if this is possible. This will cover all my scenarios of spreading and I'm really grateful for your help. Thank you so much.Allocation of estimated sales with 4 variables v4.xlsm