Besides Matching, it needs to fill up value with criteria and priority

[iceburger96]

First of all, thank you very much if you can help me solve the below Excel formula.

As you can see there are 2 sets of data. First we need to match the part number, second, we need to insert the commit number (G2) into the fulfilled QTY (D2). Now, here's the first issue I'm having, as you can see from data set 1, there are 4 lines for A001, but I only need the commit number of 20 for part A001 from data set 2 to insert into D5. Second issue, there are 4 lines for A002 as well, but I only need the commit number of 60 for part A002 from data set 2 to ONLY insert into D7.

If both of the quantity and commit number from both of the data sets are zero, then nothing to fill out at column D. And if both the quantity and commit number have values from both of the data sets then fill out the value at column G to column D.

So how do you actually solve the issues for part A001 and part A002?

Thank you in advance.

 

[Fluff]

Can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Toadstool]

Hi Iceburger96,

I have a sneaking suspicion that if there were two values of orders for a product then you would want to share the Commit No. between them. (e.g. A001 has orders for 15 and 50 but only a Commit No. value of 20)

Are my suspicions correct?

Iceburger96.xlsx
	A	B	C	D	E	F	G
1
2	PO	Part Number	Quantity	Fulfilled Qty		Part Number	Commit No.
3	PO001	A001	0	0		A001	20
4	PO002	A001	15	15		A002	60
5	PO003	A001	50	5		A003	0
6	PO004	A001	100	0		A004	0
7	PO005	A002	120	60		A005	0
8	PO006	A002	200	0		A006	20
9	PO007	A002	300	0		A007	30
10	PO008	A002	50	0		A008	30
11	PO009	A003	0	0		A009	10
12	PO010	A004	0	0		A010	0
13	PO011	A005	0	0		A011	0
14	PO012	A006	50	20		A012	0
15	PO013	A007	60	30		A013	5
16	PO014	A008	60	30		A014	10
17	PO015	A009	30	10		A015	0
Sheet1 (2)

Cell Formulas
Range	Formula
D3:D17	D3	=MIN(C3,IFERROR(INDEX($G$3:$G$9999,MATCH($B3,$F$3:$F$9999,0))-SUMIFS($D$2:$D2,$B$2:$B2,B3),0))

 

[Fluff]

Or as per description

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	PO	Part Number	Quantity	Fulfilled Qty		Part Number	Commit No.
2	PO001	A001	0	0		A001	20
3	PO002	A001	15	20		A002	60
4	PO003	A001	50	0		A003	0
5	PO004	A001	100	0		A004	0
6	PO005	A002	120	60		A005	0
7	PO006	A002	200	0		A006	20
8	PO007	A002	300	0		A007	30
9	PO008	A002	50	0		A008	30
10	PO009	A003	0	0		A009	10
11	PO010	A004	0	0		A010	0
12	PO011	A005	0	0		A011	0
13	PO012	A006	50	20		A012	0
14	PO013	A007	60	30		A013	5
15	PO014	A008	60	30		A014	10
16	PO015	A009	30	10		A015	0
Master

Cell Formulas
Range	Formula
D2:D16	D2	=IF(AND(C2>0,COUNTIFS(B$2:B2,B2,C$2:C2,">0")=1),SUMIFS(G:G,F:F,B2),0)

 

[iceburger96]

Can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi Fluff,

Ok, noted on that, will explore the XL2BB tool and the "Test Here" forum.

Thank you.

 

[iceburger96]

Hi Iceburger96,

I have a sneaking suspicion that if there were two values of orders for a product then you would want to share the Commit No. between them. (e.g. A001 has orders for 15 and 50 but only a Commit No. value of 20)

Are my suspicions correct?

Iceburger96.xlsx
	A	B	C	D	E	F	G
1
2	PO	Part Number	Quantity	Fulfilled Qty		Part Number	Commit No.
3	PO001	A001	0	0		A001	20
4	PO002	A001	15	15		A002	60
5	PO003	A001	50	5		A003	0
6	PO004	A001	100	0		A004	0
7	PO005	A002	120	60		A005	0
8	PO006	A002	200	0		A006	20
9	PO007	A002	300	0		A007	30
10	PO008	A002	50	0		A008	30
11	PO009	A003	0	0		A009	10
12	PO010	A004	0	0		A010	0
13	PO011	A005	0	0		A011	0
14	PO012	A006	50	20		A012	0
15	PO013	A007	60	30		A013	5
16	PO014	A008	60	30		A014	10
17	PO015	A009	30	10		A015	0
Sheet1 (2)

Cell Formulas
Range	Formula
D3:D17	D3	=MIN(C3,IFERROR(INDEX($G$3:$G$9999,MATCH($B3,$F$3:$F$9999,0))-SUMIFS($D$2:$D2,$B$2:$B2,B3),0))

Hi Toadstool,

Yes, you are.

Didn't expect to have solutions this fast from experienced user.

Thank you again for your reply.

Regards

 

[iceburger96]

Or as per description

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	PO	Part Number	Quantity	Fulfilled Qty		Part Number	Commit No.
2	PO001	A001	0	0		A001	20
3	PO002	A001	15	20		A002	60
4	PO003	A001	50	0		A003	0
5	PO004	A001	100	0		A004	0
6	PO005	A002	120	60		A005	0
7	PO006	A002	200	0		A006	20
8	PO007	A002	300	0		A007	30
9	PO008	A002	50	0		A008	30
10	PO009	A003	0	0		A009	10
11	PO010	A004	0	0		A010	0
12	PO011	A005	0	0		A011	0
13	PO012	A006	50	20		A012	0
14	PO013	A007	60	30		A013	5
15	PO014	A008	60	30		A014	10
16	PO015	A009	30	10		A015	0
Master

Cell Formulas
Range	Formula
D2:D16	D2	=IF(AND(C2>0,COUNTIFS(B$2:B2,B2,C$2:C2,">0")=1),SUMIFS(G:G,F:F,B2),0)

Hi Fluff,

Thanks for your reply.

Great. I have two sets of formula to use for different situations then.

Thank you.

Regards

 

[Toadstool]

Hi Toadstool,

Yes, you are.

Didn't expect to have solutions this fast from experienced user.

Thank you again for your reply.

Regards

You're welcome!
...probably more age than experience but I'll take it.