Aug 06 2021 07:33 AM
I have a spreadsheet of data, about 7k lines that I need to cost allocate. I've added a small portion below as an example. What I would like to display with a formula is the total for each cost center by document number. In other words, cost center "2539" below would display Doc# 40859728 as $5.00 and Doc# 40885398 as $5.65.
I would also like to further allocate the total for Doc# 40874160. Although one entry, for $3.81, solely belongs to cost center "2616", an additional entry for that Doc# belongs to more than one cost center, "2616" and "3571". How can I display that split? I would like Doc# 40874160 to return two results: a total of $4.02 for cost center "2616" and only $0.21 for "3571".
I have tried a pivot table, but I'm not as experienced with those and not sure if that would even help. I'm ok with formulas, but not so well with nested functions so I've not been able to figure out the best way to display this.
Thanks for any help you can provide!!
Document | Amount | Cost Center |
40886025 | $ 5.53 | 2529 |
40861530 | $ 7.70 | 3563 |
40881086 | $ 7.70 | 3561 |
40861530 | $ 9.90 | 3562 |
40881087 | $ 7.70 | 3564 |
40859728 | $ 5.00 | 2539 |
40885398 | $ 2.30 | 2539 |
40885398 | $ - | 2539 |
40885398 | $ 3.35 | 2539 |
40862560 | $ 1.31 | 2648 |
40862560 | $ 1.62 | 2648 |
40862560 | $ 1.24 | 2648 |
40862560 | $ 1.31 | 2616 |
40874160 | $ 3.81 | 2616 |
40874160 | $ 0.42 | 2616/3571 |
40857867 | $ 5.40 | 2551/3560 |
40857867 | $ 2.44 | 2551 |
40857867 | $ 9.56 | 2551 |
40881088 | $ 5.00 | 3560/3566/3575/3579 |
40885934 | $ - | 2631 |
40885934 | $ - | 2631 |
40863091 | $ 5.30 | 2615 |
40863091 | $ 5.46 | 2615 |
40863091 | $ 5.83 | 2615 |
$ 97.88 |
Aug 06 2021 09:39 AM
Yu may aggregate that with PivotTable. Cost Centres to split manually or with Power Query, when the result is
Aug 06 2021 09:49 AM
Follow up - in the above view, cost center 3571 shows a full $0.42 but I want to split it in half (between 3571 and 2616). How do I achieve this?
Aug 08 2021 02:11 PM