Index match help with division

[proficient]

Hello all,

I have shared two tables below, first one is a DATA SOURCE and second one is the DESIRED RESULT. I want the DESIRED RESULT in yellow highlighted columns in the 2nd table.

I am looking for a particular formula which can read and match the product ID from DATA SOURCE and distribute the quantity in their respective column's cell, furthermore the quantity distributed by the formula shall not exceed from the CUT QUANTITY of that particular product ID.

Book1.xlsx
	B	C	D		F	G	H	I	J	K	L
1	DATA SOURCE
2	PRODUCT ID	CUSTOMER	OCS SHIPMENT DATE		CUT QTY (3% EXTRA)	TOTAL CUT	BALANCE	TOTAL OUTPUT	BALANCE	TOTAL SENT TO WASH	SENT TO WASH BALANCE
3	5064	H & M	5-Jul		3090	3090	00	2950	-140	2500	-450
4	5066	H & M	5-Jul		4120	3500	-620	2796	-704	1022	-1774
5	5027-2	H & M	5-Jul		5150	4250	-900	3795	-455	1881	-1914
6
7	DESIRED RESULT
8	PRODUCT ID	CUSTOMER	OCS SHIPMENT DATE		CUT QTY (3% EXTRA)	TOTAL CUT	BALANCE	TOTAL OUTPUT	BALANCE	TOTAL SENT TO WASH	SENT TO WASH BALANCE
9	5066	H & M	5-Jul		1854	1854	00	1854	00	1022	-832
10	5064	H & M	5-Jul		1545	1545	00	1545	00	1545	00
11	5027-2	H & M	5-Jul		3296	3296	00	3296	00	1881	-1415
12	5066	H & M	6-Jul		1236	1236	00	942	-294	00	-942
13	5064	H & M	6-Jul		1030	1030	00	1030	00	955	-75
14	5066	H & M	7-Jul		1030	410	-620	00	-410	00	00
15	5064	H & M	7-Jul		515	515	00	375	-140	00	-375
16	5027-2	H & M	7-Jul		1854	954	-900	499	-455	00	-499
Sheet2

 

[jasonb75]

Any clues as to the logic that you use to determine how the distribution should be calculated? Without a clear explanation this is likely to remain unanswered.

 

[proficient]

Any clues as to the logic that you use to determine how the distribution should be calculated? Without a clear explanation this is likely to remain unanswered.

Thank you Jasonb75 for your reply.

I want proportional distribution based on criteria (Product ID) and cut qty 3% extra in DESIRED RESULT table. If in DATA SOURCE product ID TOTAL CUT is 3,500 units, then formula will distibute these 3,500 units in DESIRED RESULT table against product ID # 5066 like I mentioned 1854, 1030 and 410.

 

[jasonb75]

That still tells us nothing, there appears to be no logical consistency with the rate of distribution.

 

[proficient]

Hello Jasonb75,

let me rephrase all the scenario for you.

I am picking only 1 Product ID to make you understand what I am actually looking for.

Let's say the ID is 5066

As you can see in DATA SOURCE table total 3500 units have been cut in TOTAL CUT column.

I want to distribute these 3500 units in DESIRED RESULT table in 5066 IDs (as you can see there are three IDs of 5066) in FIFO method (first in first out).

The formula I am looking for will match the Product ID and distribute 1854 units in first 5066 ID as CUT QTY. with 3% of first ID of 5066 is 1854 units in DESIRED RESULT table.

Now there are 1646 units left, it will now distribute 1236 units in second ID of 5066 (as you can see CUT QTY. with 3% of second ID of 5066 is 1236 units).

Now only 410 units left but the CUT QTY. WITH 3% of third ID of 5066 is 1030, in this case it can distribute only 410 units. rest of the 620 units will be added in them once they are produced.

KEY NOTE: Quantity should not be exceeded of any product ID from its CUT QTY. WITH 3% while distributing the units.


If you need more clarity, please ask.

 

[jasonb75]

Ok, that is making more sense, but where is the fifo information coming from? There is nothing in the source to identify it.

If the 'Desired result' table will already contain some information then we need to know what is already there and what needs to be completed by the formulas.

 

[proficient]

Ok, that is making more sense, but where is the fifo information coming from? There is nothing in the source to identify it.

If the 'Desired result' table will already contain some information then we need to know what is already there and what needs to be completed by the formulas.

There are similarities in both sheets (Product ID to cut qty. with 3%) that are content. And I have put them manually. Rest of the columns are formula based. (From cut qty. with 3% to total sent to wash).

 

[jasonb75]

Some of the misunderstanding was an error on my part. While it wasn't entirely clear what you needed, I had missed that you said in post 1 that you only needed formulas for the yellow cells.

See if this does what you need, it works with the examples provided.

Book1
	B	C	D	E	F	G	H	I	J	K	L
7	DESIRED RESULT
8	PRODUCT ID	CUSTOMER	OCS SHIPMENT DATE		CUT QTY (3% EXTRA)	TOTAL CUT	BALANCE	TOTAL OUTPUT	BALANCE	TOTAL SENT TO WASH	SENT TO WASH BALANCE
9	5066	H & M	44382	1800	1854	1854	0	1854	0	1022	-832
10	5064	H & M	44382	1500	1545	1545	0	1545	0	1545	0
11	5027-2	H & M	44382	3200	3296	3296	0	3296	0	1881	-1415
12	5066	H & M	44383	1200	1236	1236	0	942	-294	0	-942
13	5064	H & M	44383	1000	1030	1030	0	1030	0	955	-75
14	5066	H & M	44384	1000	1030	410	-620	0	-410	0	0
15	5064	H & M	44384	500	515	515	0	375	-140	0	-375
16	5027-2	H & M	44384	1800	1854	954	-900	499	-455	0	-499
Sheet1

Cell Formulas
Range	Formula
F9:F16	F9	=E9*1.03
G9:G16	G9	=MEDIAN(0,F9,VLOOKUP(B9,$B$3:$G$5,6,0)-SUMIF(B$8:B8,B9,F$8:F8))
H9:H16	H9	=G9-F9
I9:I16	I9	=MEDIAN(0,G9,VLOOKUP(B9,$B$3:$I$5,8,0)-SUMIF(B$8:B8,B9,I$8:I8))
J9:J16,L9:L16	J9	=I9-G9
K9:K16	K9	=MEDIAN(0,G9,VLOOKUP(B9,$B$3:$K$5,10,0)-SUMIF(B$8:B8,B9,K$8:K8))

 

[proficient]

Jason, the formula is working perfectly! Thank you so much

Can you elaborate how MEDIAN manages this whole thing? TIA

 

[proficient]

How did you think that MEDIAN can fullfil the requirement, I have checked every step in the formula by pressing F9 function key, there are three numbers Median is dealing with. It os just amazing. Do let me know how did you think to use this formula.

Many thanks Jason ?