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

iceburger96

New Member
Joined
Jul 7, 2021
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
First of all, thank you very much if you can help me solve the below Excel formula. :)

1625646464520.png


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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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
ABCDEFG
1
2POPart NumberQuantityFulfilled QtyPart NumberCommit No.
3PO001A00100A00120
4PO002A0011515A00260
5PO003A001505A0030
6PO004A0011000A0040
7PO005A00212060A0050
8PO006A0022000A00620
9PO007A0023000A00730
10PO008A002500A00830
11PO009A00300A00910
12PO010A00400A0100
13PO011A00500A0110
14PO012A0065020A0120
15PO013A0076030A0135
16PO014A0086030A01410
17PO015A0093010A0150
Sheet1 (2)
Cell Formulas
RangeFormula
D3:D17D3=MIN(C3,IFERROR(INDEX($G$3:$G$9999,MATCH($B3,$F$3:$F$9999,0))-SUMIFS($D$2:$D2,$B$2:$B2,B3),0))
 
Upvote 0
Solution
Or as per description
+Fluff 1.xlsm
ABCDEFG
1POPart NumberQuantityFulfilled QtyPart NumberCommit No.
2PO001A00100A00120
3PO002A0011520A00260
4PO003A001500A0030
5PO004A0011000A0040
6PO005A00212060A0050
7PO006A0022000A00620
8PO007A0023000A00730
9PO008A002500A00830
10PO009A00300A00910
11PO010A00400A0100
12PO011A00500A0110
13PO012A0065020A0120
14PO013A0076030A0135
15PO014A0086030A01410
16PO015A0093010A0150
Master
Cell Formulas
RangeFormula
D2:D16D2=IF(AND(C2>0,COUNTIFS(B$2:B2,B2,C$2:C2,">0")=1),SUMIFS(G:G,F:F,B2),0)
 
Upvote 0
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.
 
Upvote 0
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
ABCDEFG
1
2POPart NumberQuantityFulfilled QtyPart NumberCommit No.
3PO001A00100A00120
4PO002A0011515A00260
5PO003A001505A0030
6PO004A0011000A0040
7PO005A00212060A0050
8PO006A0022000A00620
9PO007A0023000A00730
10PO008A002500A00830
11PO009A00300A00910
12PO010A00400A0100
13PO011A00500A0110
14PO012A0065020A0120
15PO013A0076030A0135
16PO014A0086030A01410
17PO015A0093010A0150
Sheet1 (2)
Cell Formulas
RangeFormula
D3:D17D3=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
 
Upvote 0
Or as per description
+Fluff 1.xlsm
ABCDEFG
1POPart NumberQuantityFulfilled QtyPart NumberCommit No.
2PO001A00100A00120
3PO002A0011520A00260
4PO003A001500A0030
5PO004A0011000A0040
6PO005A00212060A0050
7PO006A0022000A00620
8PO007A0023000A00730
9PO008A002500A00830
10PO009A00300A00910
11PO010A00400A0100
12PO011A00500A0110
13PO012A0065020A0120
14PO013A0076030A0135
15PO014A0086030A01410
16PO015A0093010A0150
Master
Cell Formulas
RangeFormula
D2:D16D2=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. :giggle:

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

Thank you.

Regards
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top