Excel Help for Automate Cost Allocation

Hi everyone!

I need to allocate costs into a table according to allocation rules I have in another sheet. Hopefully someone will be able to help me.

The information I have has the following format:

Activity: XXX
Service: YYY
Cost Pool 1: $$$$
Cost Pool 2: $$$$
Cost Pool N: $$$$$
(with every activity/service combination)

In a separete table I have the allocation table I need to follow in the following format:

Activity: XXX
Service: YYY
Cost Pool: 1
Allocate to driver: A124
(and so forth for every combination)

My goal is to create a sheet with Activity/Service/Driver that will give me the $$$$ per driver. i.e.

Activity: XXX
Service: YYY
Driver A124: $$$
Driver N: $$$

I've attached a small version of what the sheets in Excel will look like and hopefully it will give you a better idea of what I need to do.

Here are some more specifics:
1. The number of pools and drivers are fix and always arrange in the same way, therefore it is possible to read the table by column position rather than header.
2. The program needs read the table and given the combination ActivityID/Service SegementID/Pool allocate to 1 of the drivers according to the Distribution rule.
3. This can't be done as a "fix" distribution because the number of rows can change in the future.

Basically, the tool will search for the combinations in the white boxes and "distribute" or copy according to the rule in the yellow boxes.

THANKS!

Excel Spreadsheet:

	A	B	C	D	E	F	G
1	Info I have	Info I have	Info I have	Info I have	Distribute Here	Distribute Here	Distribute Here
2	Activity ID	ServiceSegment_ID	Pool 1	Pool 2	Driver 1012	Driver 1014	Driver 1015
3	AA129-AC	SS520-AS	100
4	AA129-AC	SS521-AS		150
5	AA130-AC	SS520-AS	50	750
6
7	For every combination	of Activity/ServSeg/Pool	corresponds 1 Driver

The distribution rule spreadsheet loks like this
Excel Spreadsheet:

	A	B	C	D
1	Activity ID	ServiceSegment_ID	Pool	Driver
2	AA129-AC	SS520-AS	1	1012
3	AA129-AC	SS521-AS	2	1015
4	AA130-AC	SS520-AS	1	1014
5	AA130-AC	SS520-AS	2	1012
6
7

THANKS!!!!!

Automate cost allocation

Excel Help for Automate Cost Allocation in Automating Data Analysis Tasks

Automate Cost Allocation