soh to order qty calculation

[Paul_au]

Hi all, I know this is a very simple solution, I'm just a bit rusty on my excel and can't find the answer.
It is a very similar problem as the question in this previous topic

I have a set of data that is itemised by order number and sku and every line pulls our current SOH for the sku
Where items repeat, SOH may not be sufficient to fulfil the order - I need to identify orders that are good to go.
Partial supply is okay example if we have 8 units on hand and order is for 10, we can partial supply the 8

book1.xlsx
	A	B	C	D
1	ID	sku	QTY ordered	SOH
2	Order 1	sku1	1	2
3	Order 2	sku2	1	3
4	Order 3	sku2	1	3
5	Order 4	sku3	1	2
6	Order 5	sku4	1	1
7	Order 6	sku5	1	3
8	Order 7	sku3	1	2
9	Order 8	sku3	1	2
10	Order 9	sku3	1	2
11	Order 10	sku3	1	2
12	Order 11	sku6	1	1
13	Order 12	sku7	1	1
14	Order 13	sku8	1	1
15	Order 14	sku9	1	2
16	Order 15	sku9	1	2
17	Order 16	sku4	1	1
18	Order 17	sku10	1	1
19	Order 18	sku10	1	1
20	Order 19	sku11	2	1
21	Order 20	sku12	1	1
22	Order 21	sku13	1	2
23	Order 22	sku13	1	2
24	Order 23	sku13	1	2
25	Order 24	sku14	20	4
26	Order 25	sku3	1	2
27	Order 25	sku15	2	126
28	Order 25	sku16	2	123
29	Order 25	sku17	2	149
30	Order 25	sku18	5	97
31	Order 25	sku19	10	8700
32	Order 25	sku20	20	307
33	Order 25	sku21	10	692
34	Order 25	sku22	20	705
35	Order 25	sku23	1	33
36	Order 25	sku24	5	17
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D2:D36	Cell Value	<0	text	NO
D2:D36	Cell Value	>0	text	NO

 

[BSALV]

Column E = qty already assigned to previous orders, so no longer in SOH

Map4 (version 1).xlsb
	A	B	C	D	E	F
1	ID	sku	QTY ordered	SOH	already assigned	(partial) supply
2	Order 1	sku1	1	2	0	1
3	Order 2	sku2	1	3	0	1
4	Order 3	sku2	1	3	1	1
5	Order 4	sku3	1	2	0	1
6	Order 5	sku4	1	1	0	1
7	Order 6	sku5	1	3	0	1
8	Order 7	sku3	1	2	1	1
9	Order 8	sku3	1	2	2	0
10	Order 9	sku3	1	2	3	0
11	Order 10	sku3	1	2	4	0
12	Order 11	sku6	1	1	0	1
13	Order 12	sku7	1	1	0	1
14	Order 13	sku8	1	1	0	1
15	Order 14	sku9	1	2	0	1
16	Order 15	sku9	1	2	1	1
17	Order 16	sku4	1	1	1	0
18	Order 17	sku10	1	1	0	1
19	Order 18	sku10	1	1	1	0
20	Order 19	sku11	2	1	0	1
21	Order 20	sku12	1	1	0	1
22	Order 21	sku13	1	2	0	1
23	Order 22	sku13	1	2	1	1
24	Order 23	sku13	1	2	2	0
25	Order 24	sku14	20	4	0	4
26	Order 25	sku3	1	2	5	0
27	Order 25	sku15	2	126	0	2
28	Order 25	sku16	2	123	0	2
29	Order 25	sku17	2	149	0	2
30	Order 25	sku18	5	97	0	5
31	Order 25	sku19	10	8700	0	10
32	Order 25	sku20	20	307	0	20
33	Order 25	sku21	10	692	0	10
34	Order 25	sku22	20	705	0	20
35	Order 25	sku23	1	33	0	1
36	Order 25	sku24	5	17	0	5
37
Blad1

Cell Formulas
Range	Formula
E2:E36	E2	=SUMIFS($C$1:C1,$B$1:B1,B2)
F2:F36	F2	=MAX(0,MIN(C2,D2-E2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C36	Expression	=$C2<>$F2	text	NO

 

[Paul_au]

Column E = qty already assigned to previous orders, so no longer in SOH

Map4 (version 1).xlsb
	A	B	C	D	E	F
1	ID	sku	QTY ordered	SOH	already assigned	(partial) supply
2	Order 1	sku1	1	2	0	1
3	Order 2	sku2	1	3	0	1
4	Order 3	sku2	1	3	1	1
5	Order 4	sku3	1	2	0	1
6	Order 5	sku4	1	1	0	1
7	Order 6	sku5	1	3	0	1
8	Order 7	sku3	1	2	1	1
9	Order 8	sku3	1	2	2	0
10	Order 9	sku3	1	2	3	0
11	Order 10	sku3	1	2	4	0
12	Order 11	sku6	1	1	0	1
13	Order 12	sku7	1	1	0	1
14	Order 13	sku8	1	1	0	1
15	Order 14	sku9	1	2	0	1
16	Order 15	sku9	1	2	1	1
17	Order 16	sku4	1	1	1	0
18	Order 17	sku10	1	1	0	1
19	Order 18	sku10	1	1	1	0
20	Order 19	sku11	2	1	0	1
21	Order 20	sku12	1	1	0	1
22	Order 21	sku13	1	2	0	1
23	Order 22	sku13	1	2	1	1
24	Order 23	sku13	1	2	2	0
25	Order 24	sku14	20	4	0	4
26	Order 25	sku3	1	2	5	0
27	Order 25	sku15	2	126	0	2
28	Order 25	sku16	2	123	0	2
29	Order 25	sku17	2	149	0	2
30	Order 25	sku18	5	97	0	5
31	Order 25	sku19	10	8700	0	10
32	Order 25	sku20	20	307	0	20
33	Order 25	sku21	10	692	0	10
34	Order 25	sku22	20	705	0	20
35	Order 25	sku23	1	33	0	1
36	Order 25	sku24	5	17	0	5
37
Blad1

Cell Formulas
Range	Formula
E2:E36	E2	=SUMIFS($C$1:C1,$B$1:B1,B2)
F2:F36	F2	=MAX(0,MIN(C2,D2-E2))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C36	Expression	=$C2<>$F2	text	NO

Thank you - I've implemented that into my real data and it looks to be perfectly correct.
I do have 500+ lines and 45 columns in the document so will take some time to validate in full.
So far, so good

 

[jtakw]

Hi,

If I understand correctly, here's another approach:

Book3.xlsx
	A	B	C	D	E
1	ID	sku	QTY ordered	SOH
2	Order 1	sku1	1	2	Good
3	Order 2	sku2	1	3	Good
4	Order 3	sku2	1	3	Good
5	Order 4	sku3	1	2	Good
6	Order 5	sku4	1	1	Good
7	Order 6	sku5	1	3	Good
8	Order 7	sku3	1	2	Good
9	Order 8	sku3	1	2	Bad
10	Order 9	sku3	1	2	Bad
11	Order 10	sku3	1	2	Bad
12	Order 11	sku6	1	1	Good
13	Order 12	sku7	1	1	Good
14	Order 13	sku8	1	1	Good
15	Order 14	sku9	1	2	Good
16	Order 15	sku9	1	2	Good
17	Order 16	sku4	1	1	Bad
18	Order 17	sku10	1	1	Good
19	Order 18	sku10	1	1	Bad
20	Order 19	sku11	2	1	Good
21	Order 20	sku12	1	1	Good
22	Order 21	sku13	1	2	Good
23	Order 22	sku13	1	2	Good
24	Order 23	sku13	1	2	Bad
25	Order 24	sku14	20	4	Good
26	Order 25	sku3	1	2	Bad
27	Order 25	sku15	2	126	Good
28	Order 25	sku16	2	123	Good
29	Order 25	sku17	2	149	Good
30	Order 25	sku18	5	97	Good
31	Order 25	sku19	10	8700	Good
32	Order 25	sku20	20	307	Good
33	Order 25	sku21	10	692	Good
34	Order 25	sku22	20	705	Good
35	Order 25	sku23	1	33	Good
36	Order 25	sku24	5	17	Good
Sheet1096

Cell Formulas
Range	Formula
E2:E36	E2	=IF(SUMIF(B$1:B1,B2,C$1:C1)<D2,"Good","Bad")

 

[Paul_au]

Thanks to both of you for the answers, they both work and either is acceptable for the task.
For the purpose of my application, a single column is going to be a better fit.

I like the first answer for the additional data it provides and would be useful in a few other scenarios I can already think of so it won't go to waste.

 

[jtakw]

You're welcome, thanks for the feedback.

 

[BSALV]

an answer can be too good ...