Create Matrix, exhaust the excess funds and avoid circular references

lukihnio

New Member
Joined
Feb 27, 2017
Messages
7
Dear All,
below is by far the most complicated case I've ever encounter. Hopefully, that is not the case for you and you will be able to help me solve this.
Table 1 and 2 are supposed to be Matrix of Stand Top Tool Type and Stand Code Description.
Table 3 is the data set where the relationship and Availability (amount) is pulled.

Question/Task#1:
I have to create an automated matrix where the relationship (availability Stand Code and Availability Top Tool Type) will be auto-generated

Question/Task#2:
Once the availability is generated, excess amounts have to reduces from the amounts of Availability. But in a way that relationship (remaining availability) is also captured.

the process is a bit complicated for me, so I hope I clearly explained the task at hand.


@Admin, I don't have too much of an experience in creating new threads and conditions which follow it, so please do accept my apologies if I have broken any rules.



Table 1
# Stand Top Tool Type Excess Availability Tool Availability Stand Code Availability State Availability Customer Reduction Adjusted Availability
1 Trucks 40,000 300,000 - - 40,000

<tbody>
</tbody>
2
Drilling 35,000 200,000 - - 35,000



Table 2
# Stand Code Description Excess Availability Tool Availability Stand Code Availability State Availability Customer Reduction Adjusted Availability
1 Trucking 50,000 - - - - -
2 Housing Construction 60,000 - - - - -
3 - - - - - - -

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>




Table 3
#Stand Stand Code Description Stand Top Tool Type Availability
1Trucks Trucking 100,000
2Trucks Drilling 200,000
3Trailers Housing Construction 50,000

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top