Function to show which value belongs to which

[mergim]

Hello all,

I am trying to make a function that shows by which quantity the "sold" quantity is supplied by. For example, the first 3 sold quantities, -50, -30 and -20 are all supplied by the first 100 (received), as the 100 can cover all those.
It will not be able to cover the next -20, as we then would be in deficit. The last -20 will then be supplied by the next "received quantity" which is 20.

Is there a way of setting up a system or a function?


Let me know if something is unclear.

Thanks a lot!

 

[Peter_SSs]

How do you envisage the results being displayed?

After showing the expected output for the above, can you also show that again if the first 3 lots sold were 50, 30 and 25?

 

[mergim]

How do you envisage the results being displayed?

After showing the expected output for the above, can you also show that again if the first 3 lots sold were 50, 30 and 25?

Hi,

The purpose of this is to show which date or when we expect to have enough material to supply the demand/sold units.
It could be nice if I had some sort of unique number that shows the link, so fx 50,30 and 20 is supplied by the first 100 - they could have the number 1.

If we go with 50, 30, 25 then only the first two 50, 30 can be supplied, the next 25 would have to take from the next quantity available.

 

[Peter_SSs]

As I asked before:

How do you envisage the results being displayed?

Could you show us what the results should look like?

 

[mergim]

As I asked before:

Could you show us what the results should look like?

Hello Peter,

Here is a more thorough explanation. I hope it helps! The Column "Link" is something I would like to have done automatically

I am trying to find a link between which purchase order will supply which work order demand. For example, the first work order, 43081340 will be supplied by the first purchase order 59747( )(1)(1) as 100.000 can cover 25.459.
The same goes for the next work order, 43081341, again, here the first purchase order 59747( )(1)(1) will also be able to supply this amount as 25.459 + 23.189 are still not higher than 100.000. When the third work order comes, 43081094, here we have multiple purchase orders that would have to supply in order to cover the demand. Here we would need PO 59747 1-2, 1-3, and 1-4. Right now, I am doing this manually, so I hope you guys can help me with some sort of function or ideas. Please, feel free to ask for more info if needed.

*work order demand = is the material consumption

SORT DATE	ITEM NUMBER	ITEM DESCRIPTION	TYPE	ORDER NUMBER	QTY	Date	Acumulated	Link
100001	123456	Apples	Purchase order	59747( )(1)(1)	100.000	27-06-2023	100.000	1
100002	123456	Apples	Purchase order	59747( )(1)(2)	100.000	27-06-2023	200.000	2
100003	123456	Apples	Purchase order	59747( )(1)(3)	100.000	11-07-2023	300.000	2
100004	123456	Apples	Purchase order	59747( )(1)(4)	100.000	11-07-2023	400.000	2
100005	123456	Apples	Purchase order	59747( )(1)(5)	100.000	25-07-2023	500.000	3
100006	123456	Apples	Purchase order	59747( )(1)(6)	100.000	25-07-2023	600.000	3
100007	123456	Apples	Work order demand	43081340	- 25.459	29-07-2023	574.541	1
100008	123456	Apples	Work order demand	43081341	- 23.189	12-08-2023	551.352	1
100009	123456	Apples	Work order demand	43081094	- 258.238	22-08-2023	293.114	2
100010	123456	Apples	Work order demand	43081342	- 23.138	26-08-2023	269.976	2
100011	123456	Apples	Purchase order	59747( )(1)(7)	100.000	05-09-2023	369.976	4
100012	123456	Apples	Purchase order	59747( )(1)(8)	100.000	05-09-2023	469.976	5
100013	123456	Apples	Work order demand	43081343	- 28.908	09-09-2023	441.068	2
100014	123456	Apples	Work order demand	43081098	- 234.584	18-09-2023	206.484	3
100015	123456	Apples	Purchase order	59747( )(1)(10)	100.000	19-09-2023	306.484	5
100016	123456	Apples	Purchase order	59747( )(1)(9)	100.000	19-09-2023	406.484	6
100017	123456	Apples	Work order demand	43081344	- 29.070	23-09-2023	377.414	4
100018	123456	Apples	Purchase order	59747( )(1)(11)	100.000	03-10-2023	477.414	6
100019	123456	Apples	Purchase order	59747( )(1)(12)	100.000	03-10-2023	577.414	6
100020	123456	Apples	Work order demand	43081345	- 29.851	06-10-2023	547.564	4
100021	123456	Apples	Work order demand	43081105	- 234.024	10-10-2023	313.539	6
100022	123456	Apples	Work order demand	43081121	- 294.219	03-11-2023	19.320	6
100023	123456	Apples	Purchase order	59747( )(1)(14)	100.000	07-11-2023	119.320	7
100024	123456	Apples	Purchase order	59747( )(1)(13)	100.000	07-11-2023	219.320	7
100025	123456	Apples	Purchase order	59747( )(1)(15)	100.000	14-11-2023	319.320	7
100026	123456	Apples	Work order demand	43081127	- 296.003	28-11-2023	23.317	7
100027	123456	Apples	Purchase order	59747( )(1)(16)	100.000	05-12-2023	123.317	8
100028	123456	Apples	Purchase order	59747( )(1)(17)	100.000	19-12-2023	223.317	8
100029	123456	Apples	Purchase order	59747( )(1)(18)	100.000	19-12-2023	323.317	8
100030	123456	Apples	Work order demand	43081135	- 304.108	26-12-2023	19.209	8

 

[Peter_SSs]

With your further explanation, I understand how the two green values below are arrived at but after that I am struggling and I think that is because I don't understand how you came up with the yellow values. From what I can see those rows appear to be six separate order numbers, so why do, for example, three of them get the same value (2) in the Link column?

mergim.xlsm
	D	E	F	G	H	I
1	TYPE	ORDER NUMBER	QTY	Date	Acumulated	Link
2	Purchase order	59747( )(1)(1)	100	27/06/2023	100	1
3	Purchase order	59747( )(1)(2)	100	27/06/2023	200	2
4	Purchase order	59747( )(1)(3)	100	11/07/2023	300	2
5	Purchase order	59747( )(1)(4)	100	11/07/2023	400	2
6	Purchase order	59747( )(1)(5)	100	25/07/2023	500	3
7	Purchase order	59747( )(1)(6)	100	25/07/2023	600	3
8	Work order demand	43081340	-25.459	29/07/2023	574.541	1
9	Work order demand	43081341	-23.189	12/08/2023	551.352	1
10	Work order demand	43081094	-258.238	22/08/2023	293.114	2
11	Work order demand	43081342	-23.138	26/08/2023	269.976	2
Sheet1

 

[mergim]

With your further explanation, I understand how the two green values below are arrived at but after that I am struggling and I think that is because I don't understand how you came up with the yellow values. From what I can see those rows appear to be six separate order numbers, so why do, for example, three of them get the same value (2) in the Link column?

mergim.xlsm
	D	E	F	G	H	I
1	TYPE	ORDER NUMBER	QTY	Date	Acumulated	Link
2	Purchase order	59747( )(1)(1)	100	27/06/2023	100	1
3	Purchase order	59747( )(1)(2)	100	27/06/2023	200	2
4	Purchase order	59747( )(1)(3)	100	11/07/2023	300	2
5	Purchase order	59747( )(1)(4)	100	11/07/2023	400	2
6	Purchase order	59747( )(1)(5)	100	25/07/2023	500	3
7	Purchase order	59747( )(1)(6)	100	25/07/2023	600	3
8	Work order demand	43081340	-25.459	29/07/2023	574.541	1
9	Work order demand	43081341	-23.189	12/08/2023	551.352	1
10	Work order demand	43081094	-258.238	22/08/2023	293.114	2
11	Work order demand	43081342	-23.138	26/08/2023	269.976	2
Sheet1

Hello,

So in order to cover the quantity from work order 43081094 (258,238), you would need the quantity from PO 59747 1-2, 1-3 and 1-4 otherwise we would not have enough. I see in your table it says 100, but it should be 100000 like in my table. I hope it makes sense.

 

[Peter_SSs]

I see in your table it says 100, but it should be 100000 like in my table.

Ah, you must use "." as your thousands separator. For me "." is the decimal separator so Excel has converted to my notation. Perhaps for the future sample data you could try XL2BB

I hope it makes sense.

Not really. I'm struggling to understand the full logic of how you have come up (manually) with the numbers in the link column. At this stage I certainly cannot see a formula to produce those numbers.

 

[mergim]

Ah, you must use "." as your thousands separator. For me "." is the decimal separator so Excel has converted to my notation. Perhaps for the future sample data you could try XL2BB


Not really. I'm struggling to understand the full logic of how you have come up (manually) with the numbers in the link column. At this stage I certainly cannot see a formula to produce those numbers.

Hello I simply just calculate by adding and substracting the numbers.
Maybe you should ask the questions, which POs will cover this amount (work order), e.g. with work order 43081094, 258238 kg, in order to cover the quantity, what POs do you need?

This is very complex

 

[mergim]

Ah, you must use "." as your thousands separator. For me "." is the decimal separator so Excel has converted to my notation. Perhaps for the future sample data you could try XL2BB


Not really. I'm struggling to understand the full logic of how you have come up (manually) with the numbers in the link column. At this stage I certainly cannot see a formula to produce those numbers.

Hello Peter,

I may have found another way to explain the issue, please see table below (I have added a picture as well).
Imagine that we have a demand of 25000 like the first row of the table under column "Demand", and the first PO is 100000. As first PO (100000) is higher than first demand (25000) then it should say covered as you see under column Coverage. I have simply used the formula If(sum(B2+B3)>=sum(A2+A3);"Covered";"Not Covered").
Where it gets tricky is when I do the same task for the next demand. Here I look at, can the first PO of 100000 cover first demand (25000) and second demand (100000) - here, the answer is no, because 125000 is more than 100000. So, to cover both demand first and second demand, you need first PO 100000 and second PO (50000).
The function should be something like, if PO cannot cover this amount, use second PO. If it still doesnt cover, use also PO 3 etc.

I hope it makes sense!

Demand	Purchase Order	Coverage
25000​	100000​	Covered
100000​	50000​
50000​	30000​
40000​	10000​
20000​	20000​
20000​	20000​
20000​	20000​
20000​	20000​
20000​	20000​
20000​	20000​
20000​	20000​
20000​	20000​