Allocating DIvision spending to project (2 tables to 1)

Attached is a Sample document. I use VB pretty often just to do simple things like making a command to convert formulas to values and always having the active cell's row highlighted. This is a little bit harder, and I can't seem to figure out anywhere to start. Any help would be appreciated.

Document: (also attached): http://www.mediafire.com/?d4zy1jrz3ti

I have how much money departments spent each quarter, and another table that has the what projects each department was working on. I basically need to break up the money spent into specific projects.

Here is an example (this data is highlighted in red in the sample document):

Data to be organized:

Please Login or Register  to view this content.

And in Breakdown I have the following lines with Division ‘Candy Making’:

Please Login or Register  to view this content.

The resulting data (in a new sheet): should be:

Please Login or Register  to view this content.

It basically made a line for each project in ‘Breakdown’ that contains division ‘Candy Making’, copied the area (‘Around) along with division (‘candy making’) and project (X5555 and PLMN), it then multiplied the ratio of each project to the Q1 spending in ‘Data to be organized’. So I took 8/9 [9 is the sum all the projects in the division]*100 and then 1/9*100.

Note: Area doesn't matter to the allocation but it needs to be carried along with the data to the new sheet, and there may be the same department with different areas that need their own lines (but are allocated with the same breakdown)

To replicate your expected results with one common formula for entire matrix, without use of helper cells, you would be looking at something like:

Please Login or Register  to view this content.

The above is not particularly efficient however ... used against large data sets it will perform poorly.

If you can use helpers let us know as you will be able to improve the efficiency of the calculations.

Per the note at MrExcel - if you x-post in multiple forums ensure you provide links accordingly

http://www.mrexcel.com/forum/showthread.php?t=478832

we ask you divulge links so as to prevent/limit possibility of members wasting their free time composing a solution to a problem which may have been resolved elsewhere.

Neither forum in this case precludes x-posting (many do) but both do ask you follow this very simple rule.

Originally Posted by minimacros

The formula works well assuming I have the columns (A,B,C) Area, Division, & Project already filled in, the problem is I dont have that sheet at all.

I might be mistaken but isn't End Result [Cols B & C] simply a copy of Breakdown ? Albeit with a 1:1 assumption made between Project & Area which can be calculated [Col A] from data per the 'Data to be Organized sheet' ?

Depending on Volume of data it would be quite a trivial exercise to setup End Result in lieu of the data in the data sheets (ie use pre-emptive ifs to determine as to whether or not data is to be returned for any given row)