Excel formula for inventory pulling

Candy2017

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hello, I have one question on my project.
The customer pulled the Parts No. in Table A with the specific qty, and I have the inventory report for these Parts No. with different qty in separate POs.
And now I need to pull out the information in Table C from Tabel B per Table A.

I am wondering if this could be done with Array Formula or any other recommended way?
Copy of Invoicing pulling Template_0125.xlsx
ABCDEFGHIJKLMNO
1Table ATable BTable C
2Part NoSum of Outstanding GRN QtyGRN NumberGRN LinePart NoCPO NumCPO LineConfirmed Receipt ETAOutstanding GRN QtyGRN NumberGRN LineOutstanding GRN Qty
3A13845138920019A143003012/4/20203
4A242845138940018A144005012/4/20206
5A340845138940017A145009012/4/20203
6845139320036A146004012/10/20206
7845140360008A14700501/8/20213
8845139320019A248014012/10/202069
9845139320024A249015012/10/202033
10845139320037A250014012/10/202012
11845139710006A251017012/23/202030
12845139710037A252015012/23/2020120
13845139880013A253013012/29/202021
14845140360028A25401801/8/202160
15845139470038A355032012/17/202010
16845139470002A356020012/17/202010
17845139470005A357004012/17/202040
18845140140055A35800501/4/20215
19845140140001A35903201/4/202150
20845140140019A36003301/4/202110
21845140140018A36103301/4/202135
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
M2Cell ValueduplicatestextNO
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,966
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this for A1 Product.
Book1
ABCDEFGHIJKLMNOP
1Table ATable BTable C
2Part NoSum of Outstanding GRN QtyGRN NumberGRN LinePart NoCPO NumCPO LineConfirmed Receipt ETAOutstanding GRN QtyGRN NumberGRN LineOutstanding GRN Qty
3A138451389219A1433012/4/20203A1193
4A2428451389418A1445012/4/20206A1186
5A3408451389417A1459012/4/20203A1173
68451393236A1464012/10/20206A1366
7845140368A147501/8/20213A183
88451393219A24814012/10/202069   
98451393224A24915012/10/202033   
108451393237A25014012/10/202012   
11845139716A25117012/23/202030   
128451397137A25215012/23/2020120   
138451398813A25313012/29/202021   
148451403628A2541801/8/202160
158451394738A35532012/17/202010
16845139472A35620012/17/202010
17845139475A3574012/17/202040
188451401455A358501/4/20215
19845140141A3593201/4/202150
208451401419A3603301/4/202110
218451401418A3613301/4/202135
Sheet1
Cell Formulas
RangeFormula
M3:M13M3=IFERROR(INDEX($G$3:$G$21,SMALL(IF($G$3:$G$21=$A$3,ROW($G$3:$G$21)-ROW($G$3)+1),ROWS($M$1:M1))),"")
N3:N13N3=IFERROR(INDEX($F$3:$F$21,SMALL(IF($G$3:$G$21=$A$3,ROW($G$3:$G$21)-ROW($G$3)+1),ROWS($N$1:N1))),"")
O3:O13O3=IFERROR(INDEX($K$3:$K$21,SMALL(IF($G$3:$G$21=$A$3,ROW($G$3:$G$21)-ROW($G$3)+1),ROWS($O$1:O1))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
Press CTRL+SHIFT+ENTER to enter array formulas.
A2 42 or 12

Invoice Pull.xlsm
ABCDEFGHIJKLMNO
1Table ATable BTable C
2Part NoSum of Outstanding GRN QtyGRN NumberGRN LinePart NoCPO NumCPO LineConfirmed Receipt ETAOutstanding GRN QtyGRN NumberGRN LineOutstanding GRN Qty
3A138451389219A1433012/4/2020384513892193
4A2128451389418A1445012/4/2020684513894173
5A3408451389417A1459012/4/202038451403683
6 8451393236A1464012/10/20206845139323712
7 845140368A147501/8/2021384513947540
8 8451393219A24814012/10/202069   
9 8451393224A24915012/10/202033   
10 8451393237A25014012/10/202012   
11 845139716A25117012/23/202030   
12 8451397137A25215012/23/2020120   
13 8451398813A25313012/29/202021   
14 8451403628A2541801/8/202160   
15 8451394738A35532012/17/202010   
16 845139472A35620012/17/202010   
17845139475A3574012/17/202040   
188451401455A358501/4/20215   
19845140141A3593201/4/202150   
208451401419A3603301/4/202110   
218451401418A3613301/4/202135   
ورقة1
Cell Formulas
RangeFormula
M3:M21M3=IFERROR(INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/(IFERROR(((--($G$3:$G$21=$A$3))/(--($K$3:$K$21=$B$3))),0)+IFERROR(((--($G$3:$G$21=$A$4))/(--($K$3:$K$21=$B$4))),0)+IFERROR(((--($G$3:$G$21=$A$5))/(--($K$3:$K$21=$B$5))),0)),ROWS($M$3:M3))),"")
N3:N21N3=IFERROR(INDEX($F$3:$F$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/(IFERROR(((--($G$3:$G$21=$A$3))/(--($K$3:$K$21=$B$3))),0)+IFERROR(((--($G$3:$G$21=$A$4))/(--($K$3:$K$21=$B$4))),0)+IFERROR(((--($G$3:$G$21=$A$5))/(--($K$3:$K$21=$B$5))),0)),ROWS($M$3:N3))),"")
O3:O21O3=IFERROR(INDEX($K$3:$K$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/(IFERROR(((--($G$3:$G$21=$A$3))/(--($K$3:$K$21=$B$3))),0)+IFERROR(((--($G$3:$G$21=$A$4))/(--($K$3:$K$21=$B$4))),0)+IFERROR(((--($G$3:$G$21=$A$5))/(--($K$3:$K$21=$B$5))),0)),ROWS($M$3:O3))),"")
A3:A16A3=IFERROR(INDEX($G$3:$G$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/(--(MATCH($G$3:$G$21,$G$3:$G$21,0)=(ROW($G$3:$G$21)-ROW($G$3)+1))),ROWS($A$3:A3))),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Dossfm0q

Banned User
Joined
Mar 9, 2009
Messages
570
Office Version
  1. 2019
Platform
  1. Windows
correction

المصنف1
ABCDEFGHIJKLMNO
1Table ATable BTable C
2Part NoSum of Outstanding GRN QtyGRN NumberGRN LinePart NoCPO NumCPO LineConfirmed Receipt ETAOutstanding GRN QtyGRN NumberGRN LineOutstanding GRN Qty
3A138451389219A1433012/4/2020384513892193
4A2428451389418A1445012/4/2020684513894173
5A3408451389417A1459012/4/202038451403683
6 8451393236A1464012/10/2020684513947540
7 845140368A147501/8/20213   
8 8451393219A24814012/10/202069   
9 8451393224A24915012/10/202033   
10 8451393237A25014012/10/202012   
11 845139716A25117012/23/202030   
12 8451397137A25215012/23/2020120   
13 8451398813A25313012/29/202021   
14 8451403628A2541801/8/202160   
15 8451394738A35532012/17/202010   
16 845139472A35620012/17/202010   
17845139475A3574012/17/202040   
188451401455A358501/4/20215   
19845140141A3593201/4/202150   
208451401419A3603301/4/202110   
218451401418A3613301/4/202135   
ورقة1
Cell Formulas
RangeFormula
M3:M21M3=IFERROR(INDEX($E$3:$E$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/((--($G$3:$G$21=$A$3))*(--($K$3:$K$21=$B$3))+(--($G$3:$G$21=$A$4))*(--($K$3:$K$21=$B$4))+(--($G$3:$G$21=$A$5))*(--($K$3:$K$21=$B$5))),ROWS($M$3:M3))),"")
N3:N21N3=IFERROR(INDEX($F$3:$F$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/((--($G$3:$G$21=$A$3))*(--($K$3:$K$21=$B$3))+(--($G$3:$G$21=$A$4))*(--($K$3:$K$21=$B$4))+(--($G$3:$G$21=$A$5))*(--($K$3:$K$21=$B$5))),ROWS($N$3:N3))),"")
O3:O21O3=IFERROR(INDEX($K$3:$K$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/((--($G$3:$G$21=$A$3))*(--($K$3:$K$21=$B$3))+(--($G$3:$G$21=$A$4))*(--($K$3:$K$21=$B$4))+(--($G$3:$G$21=$A$5))*(--($K$3:$K$21=$B$5))),ROWS($O$3:O3))),"")
A3:A16A3=IFERROR(INDEX($G$3:$G$21,AGGREGATE(15,6,(ROW($G$3:$G$21)-ROW($G$3)+1)/(--(MATCH($G$3:$G$21,$G$3:$G$21,0)=(ROW($G$3:$G$21)-ROW($G$3)+1))),ROWS($A$3:A3))),"")
 

Candy2017

New Member
Joined
Feb 2, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Thank you very much for your quick help!
I am Sorry, seems I did not make the request clear. I need to use the qty in Table B per the Confirmed Receipt ETA, from oldest to newest. Table B is already sorted by the date.
So there are three situations:

For part A1: the pulled qty is 3, and I need to get 84513892 / 0019 / Confirmed Receipt ETA, 12/4/2020/qty 3 from table B. But as the first three lines are with the same 12/4/2020, so it is ok to use any one of them. But normally using the 1st line is simple.

For part A2: the pulled qty is 42, and I need to get partial qty 42 from the line 84513932 / 0019/ Confirmed Receipt ETA, 12/10/2020/qty 69.
For part A3: the pulled qty is 40, and I need to get three lines, 84513947 / 0038/qty 10 and 84513947/ 0002 /qty 10, and partial qty 20 from 84513947 / 0005 / qty 40.

Can this be resolved in one Array Formula or have to be in a separate formula? Thank you!!
 

Forum statistics

Threads
1,136,196
Messages
5,674,372
Members
419,505
Latest member
tismail

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
Top