VBA - Assign Value Of Cells Base On The Total Value Column And Row

i have a problem below:
4-16-2015 12-34-02 PM.png
as you can see the picture above:
+The data with yellow highlighted is the available data.
+The cells with a question mark is my expected data.
The problem is my expected data have to satisfy the condition column and condition row (yellow highlighted). In particular, in Column 2, the total value of cells(2,2) and cells(3,2) have to be equal the value of cells(1,2). Moreover, the total value of cells(2,2), cells(2,3) and cells(2,4) have to be equal the value of cells(2,1) as well.

I am unable to understand your requirement

Please make it more descriptive for my better understanding

Not sure which is your available data and which one is your expected result

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

The reply above is my expected result. I attach the exercise file (have not been solved yet) for you to understand.
Phan bo nguoi cho tung Phieu chi - Copy.xlsx

The yellow highlighted is the available data.

Still you are missing a logic.

The data and the expected result are well known to you so it is easy for you to understand how to arrive the result.

But for us it is a new set of data and we don't have time to sit and investigate what is mentioned there and how.

Please explain your logic with a simple set of data instead of projecting a large file.

Originally Posted by longnguyen93

The yellow highlighted is the available data.

I can only see one such cell in the workbook (?)

Regards

You need to include the logic in the samples, when I look at it I just see highlighted rows with illogical formulas.

Here's what I see. This problem looks to me like a basic "solve a system of linear equations" like you learned about in your secondary algebra classes: http://www.purplemath.com/modules/systlin1.htm Back then, the problems were 2 or 3 equations with 2 or 3 unkowns. In the example given in post 9, you have ~800 equations in ~25000 unkowns.

You should recall from your previous education that a problem with more unkowns than equations will likely not have a single, unique solution, but will have an infinite number of possible solutions. You may have to assume values for 24000 of those unkowns and then determine what that means for the remaining unkowns.

Looking at the problem that way, Excel has two tools that are usually used for these kinds of problems. Excel has built in matrix functions (MINVERSE() and MMULT() for example) that can be used to solve systems of equations. There is also the Solver add-in for numerical algorithms to solve such problems.

The main problem will be, that your problem is much larger than these tools will likely handle. I do not know the exact limit on the size of matrix that the MINVERSE() function will handle, but I suspect it is much smaller than this problem will need.

Solver as it comes with Excel is limited to 100-200 unkowns. If you are willing to pay for the premium Solver package, you will be able to use upwards of 2000 unkowns (aka decision variables). It is still smaller than the problem you describe.

My first suggestion would be to scale the problem back to something much smaller (maybe 10 x 3) to develop the algorithms. Once you have the algorithms worked out, then you can see how readily they will scale up to the problem at hand.