I have an Excel technical problem to split the cost between divisions. Please
help to create a macro to replace my manual process.

e.g. I have the following data
Division Cost
General 100
Admin 150

If the Division is General, the cost need be split by Key 1, if it is Admin,
split by Key 2, and the original lines will be replaced by three different
lines

Division A Division B Division C
Key1 50% 30% 20%
Key2 70% 0 30%

the result:

Division Cost
Division A 50
Division B 30
Division C 20
Division A 105
Division B 0
Division C 45

Please help! Thanks!!

In reality, do you have far more data than this? If so, please give
further details.

Pete

On Jun 17, 7:48*am, Binyaaust <Binyaa...@discussions.microsoft.com>
wrote:
> I have an Excel technical problem to split the cost between divisions. Please
> help to create a macro to replace my manual process.
>
> e.g. I have the following data
> Division * * * *Cost
> General 100
> Admin * * * 150
>
> If the Division is General, the cost need be split by Key 1, if it is Admin,
> split by Key 2, and the original lines will be replaced by three different
> lines
>
> * * * * Division A * * *Division B * * *Division C
> Key1 * *50% * * 30% * * 20%
> Key2 * *70% * * 0 * * * 30%
>
> the result:
>
> Division * * * *Cost
> Division A * * *50
> Division B * * *30
> Division C * * *20
> Division A * * *105
> Division B * * *0
> Division C * * *45
>
> Please help! Thanks!!

Here is the first line of my real data:

YEAR PERD PC ACCT AMT DESC Division
2008 1 611 11600 450 236203 KLIKON K9

If the Division is K9, I would like to delete this line and replace it with
5 lines for each sub-division based on the key allocation below. The AMT
column will be calculated based on the % of the key.

KEYTYPE AIF AII AIP GAP CTS TOTAL
K9 17.00% 22.80% 18.10% 2.50% 39.60% 100.00%

It should have result:

YEAR PERD PC ACCT AMT DESC Division
2008 1 611 11600 76.5 236203 KLIKON AIF
2008 1 611 11600 102.6 236203 KLIKON AII
2008 1 611 11600 81.45 236203 KLIKON AIP
2008 1 611 11600 11.25 236203 KLIKON GAP
2008 1 611 11600 178.2 236203 KLIKON CTS

All information will be the same as original line except the re-calculated
AMT and Division name.

Hope this explain the detail to you.

Thanks,

"Pete_UK" wrote:

> In reality, do you have far more data than this? If so, please give
> further details.
>
> Pete
>
> On Jun 17, 7:48 am, Binyaaust <Binyaa...@discussions.microsoft.com>
> wrote:
> > I have an Excel technical problem to split the cost between divisions. Please
> > help to create a macro to replace my manual process.
> >
> > e.g. I have the following data
> > Division Cost
> > General 100
> > Admin 150
> >
> > If the Division is General, the cost need be split by Key 1, if it is Admin,
> > split by Key 2, and the original lines will be replaced by three different
> > lines
> >
> > Division A Division B Division C
> > Key1 50% 30% 20%
> > Key2 70% 0 30%
> >
> > the result:
> >
> > Division Cost
> > Division A 50
> > Division B 30
> > Division C 20
> > Division A 105
> > Division B 0
> > Division C 45
> >
> > Please help! Thanks!!
>
>