Loop Through Record or Multirow Formula

[Shank]

Hi All, 

Good day!

I am stuck with a task where i need to allocate the quantity in batches to order can someone help me on this. Below is the scenario.

From table 2 I need to allocate the quantity in batches to order. for example. batch ZMR1 is having 450 units and it can full fill order 1, 2, 3 and 50 units of order 4. but for order 4 we have requirement of 150 units (50 units is consumed from ZMR1 and Rest 100 units should be taken from batch ZMR2. Can someone help on this i tried using multirow? but I was not able to achieve desired output. 

Table 1: 

Order No	Product	units
1	A	100
2	A	200
3	A	100
4	A	150
5	A	120
6	A	130
7	A	110
8	A	145
9	A	180
10	A	170

Table 2

Product	Batch	Quantity
A	ZMR1	450
A	ZMR2	210
A	ZMR3	150

Desired Output

Order No	Product	Quantity	Batch	Opening Balance	Units consumed	Closing Balance
1	A	100	ZMR1	450	-100	350
2	A	200	ZMR1	350	-200	150
3	A	100	ZMR1	150	-100	50
4	A	150	ZMR1	50	-150	-100
4	A	150	ZMR2	210	-100	110
5	A	120	ZMR2	110	-120	-10
5	A	120	ZMR3	150	-10	140
6	A	130	ZMR3	140	-130	10
7	A	110	ZMR3	10	-110	-100

[caltang]

Hi @Shank,

I am able to combine the tables into the first portion of your desired output: 

Order No	Product	Quantity	Batch
1	A	100	ZMR1
2	A	200	ZMR1
3	A	100	ZMR1
4	A	150	ZMR1
4	A	150	ZMR2
5	A	120	ZMR2
5	A	120	ZMR3
6	A	130	ZMR3
7	A	110	ZMR3

However, in order to calculate Opening Balance, Units consumed, and Closing Balance, is something that I am stuck at myself. 

Hopefully my workflow gives you some ideas on how to proceed from there. I'll continue trying to achieve your output, but the difficulty is because of the existence of the Order No and two or more batches it belongs to. The relationship becomes one to many, and that affects your formulas by row.

Best,

Cal

[Christina_H]

Here's a macro that I think does what you need.

[Shank]

@Christina_H 

Good day!

Many thanks for helping me on this one.

This works perfectly fine for one scenario. But I have multiple order and multiple product how can we fix it?   below is the example.

I tried to change something in macro, but it did not work well :( 

Table 1: 

Order No	Product	units
1	A	100
2	A	200
3	A	100
4	A	150
5	A	120
6	B	130
7	B	110
8	C	145
9	C	180
10	C	170

Table 2

Product	Batch	Quantity
A	ZMR1	450
A	ZMR2	210
A	ZMR3	150
B	ZBR1	50
B	ZBR2	150
C	ZCR1	150
C	ZCR2	150

[Shank]

Hi @caltang, 

Thanks for taking a time to help me on this problem :) 

Even, I achieved the same as what you did but by combining the two tables, but I was stuck after that on getting the closing balance as we had Mutiple batches. 

Yes, please let me know if you get any solution. also, I will keep you posted I get the desired result :) 

[Christina_H · Accepted solution]

Hi @Shank

I've made some changes to the macro to work with multiple products.  I think it's doing what you need now.

[Raj]

@Christina_H  thanks for sharing I was also looking for something similar

[Shank]

@Christina_H 

Thanks for the sharing the workaround. It perfectly works.