Create Matrix, exhaust the excess funds and avoid circular references

[lukihnio]

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
[TABLE="width: 1152"]
<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]#[/TD]
[TD] Stand Top Tool Type [/TD]
[TD] Excess [/TD]
[TD]Availability Tool[/TD]
[TD] Availability Stand Code[/TD]
[TD] Availability State [/TD]
[TD] Availability Customer [/TD]
[TD] Reduction [/TD]
[TD] Adjusted Availability [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] Trucks[/TD]
[TD] 40,000[/TD]
[TD][/TD]
[TD] 300,000[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 40,000[/TD]
[TD] [TABLE="width: 110"]
<tbody>[TR]
[TD="class: xl177, width: 110"] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD] Drilling[/TD]
[TD] 35,000[/TD]
[TD][/TD]
[TD] 200,000[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 35,000[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Table 2 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]#[/TD]
[TD] Stand Code Description [/TD]
[TD] Excess [/TD]
[TD] Availability Tool[/TD]
[TD] Availability Stand Code[/TD]
[TD] Availability State [/TD]
[TD] Availability Customer[/TD]
[TD] Reduction [/TD]
[TD] Adjusted Availability [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] Trucking [/TD]
[TD] 50,000[/TD]
[TD] -[/TD]
[TD][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] Housing Construction [/TD]
[TD] 60,000[/TD]
[TD] -[/TD]
[TD][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD][/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
</tbody>[/TABLE]




Table 3
[TABLE="width: 571"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]#[/TD]
[TD]Stand Stand Code Description [/TD]
[TD]Stand Top Tool Type[/TD]
[TD] [/TD]
[TD] Availability[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Trucks [/TD]
[TD]Trucking [/TD]
[TD] [/TD]
[TD] 100,000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Trucks [/TD]
[TD]Drilling[/TD]
[TD] [/TD]
[TD] 200,000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Trailers [/TD]
[TD] Housing Construction[/TD]
[TD] [/TD]
[TD] 50,000[/TD]
[/TR]
</tbody>[/TABLE]