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

 

[proficient]

Hello Jason, Your provided formula is working fine on example sheet but is not working in original sheet and skipping output result (showing blank). I don't know why.

Note: I have replaced VLOOKUP with INDEX & MATCH and I hope it won't affect on formula performance as it is working fine in example sheet.

 

[proficient]

=MEDIAN(0,O3,INDEX(DataSource!$Q$3:$Q$247,MATCH('Shipment plan'!C3,DataSource!$E$3:$E$247,0))-SUMIF(DataSource!E3,'Shipment plan'!C3,'Shipment plan'!$P$2:P2))

I am getting correct answer if I use above formula, instead of range I just used single cell formula.

 

[jasonb75]

The first range in the sumif part was looking at the wrong sheet. I'm assuming that the formulas are in the shipment plan sheet so you don't need to use the sheet name there.

I think that this should be right but it is only best guess without having seen the layout of your sheets.

=MEDIAN(0,$O3,INDEX(DataSource!$Q$3:$Q$247,MATCH($C3,DataSource!$E$3:$E$247,0))-SUMIF($C$2:$C2,$C3,$P$2:$P2))

For Total Cut, the correct result should never be less than 0 or more than Cut Qty (3% extra) so those are used as the first 2 values of median.
The 3rd value is what is outstanding for the current product (initial qty from the source table minus total of previous rows for the same product in the shipment plan).
Depending on the qty already shipped, if there is still more to ship after the current row then the result will be greater than Cut Qty and greater than 0. In this case Cut Qty will be the median value because it is greater than 0 but less then the amount remaining to ship.

If the amount remaining to ship is greater than 0 but less than Cut Qty then the amount remaining to ship will be the median. If it is less than 0 (overship calculated) then 0 will be the median.

You may have seen other threads where =MIN(MAX(....)) has been used, which works on a similar basis.

 

[proficient]

I'll check the formula tomorrow and let you know.

Thank you for the detailed explanation.

 

[proficient]

Hello Jason, I am sharing link of original file, please download and check.

I have placed all the formula in Shipment Plan tab and highlighted the columns with yellow color and extracting the data from DataSource tabs.

LINK:

Dropbox

www.dropbox.com

 

[jasonb75]

I'll have a look at it when I get chance but that might not be for a couple of days.

 

[jasonb75]

I don't see what the issue is, the formula in post 13 is correct for P13. The only reason it is not working is because you haven't changed it.