Excel formula for inventory pulling

[Candy2017]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table A				Table B								Table C
2	Part No	Sum of Outstanding GRN Qty			GRN Number	GRN Line	Part No	CPO Num	CPO Line	Confirmed Receipt ETA	Outstanding GRN Qty		GRN Number	GRN Line	Outstanding GRN Qty
3	A1	3			84513892	0019	A1	43	0030	12/4/2020	3
4	A2	42			84513894	0018	A1	44	0050	12/4/2020	6
5	A3	40			84513894	0017	A1	45	0090	12/4/2020	3
6					84513932	0036	A1	46	0040	12/10/2020	6
7					84514036	0008	A1	47	0050	1/8/2021	3
8					84513932	0019	A2	48	0140	12/10/2020	69
9					84513932	0024	A2	49	0150	12/10/2020	33
10					84513932	0037	A2	50	0140	12/10/2020	12
11					84513971	0006	A2	51	0170	12/23/2020	30
12					84513971	0037	A2	52	0150	12/23/2020	120
13					84513988	0013	A2	53	0130	12/29/2020	21
14					84514036	0028	A2	54	0180	1/8/2021	60
15					84513947	0038	A3	55	0320	12/17/2020	10
16					84513947	0002	A3	56	0200	12/17/2020	10
17					84513947	0005	A3	57	0040	12/17/2020	40
18					84514014	0055	A3	58	0050	1/4/2021	5
19					84514014	0001	A3	59	0320	1/4/2021	50
20					84514014	0019	A3	60	0330	1/4/2021	10
21					84514014	0018	A3	61	0330	1/4/2021	35
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2	Cell Value	duplicates	text	NO

 

[maabadi]

Try this for A1 Product.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Table A				Table B								Table C
2	Part No	Sum of Outstanding GRN Qty			GRN Number	GRN Line	Part No	CPO Num	CPO Line	Confirmed Receipt ETA	Outstanding GRN Qty		GRN Number	GRN Line	Outstanding GRN Qty
3	A1	3			84513892	19	A1	43	30	12/4/2020	3		A1	19	3
4	A2	42			84513894	18	A1	44	50	12/4/2020	6		A1	18	6
5	A3	40			84513894	17	A1	45	90	12/4/2020	3		A1	17	3
6					84513932	36	A1	46	40	12/10/2020	6		A1	36	6
7					84514036	8	A1	47	50	1/8/2021	3		A1	8	3
8					84513932	19	A2	48	140	12/10/2020	69
9					84513932	24	A2	49	150	12/10/2020	33
10					84513932	37	A2	50	140	12/10/2020	12
11					84513971	6	A2	51	170	12/23/2020	30
12					84513971	37	A2	52	150	12/23/2020	120
13					84513988	13	A2	53	130	12/29/2020	21
14					84514036	28	A2	54	180	1/8/2021	60
15					84513947	38	A3	55	320	12/17/2020	10
16					84513947	2	A3	56	200	12/17/2020	10
17					84513947	5	A3	57	40	12/17/2020	40
18					84514014	55	A3	58	50	1/4/2021	5
19					84514014	1	A3	59	320	1/4/2021	50
20					84514014	19	A3	60	330	1/4/2021	10
21					84514014	18	A3	61	330	1/4/2021	35
Sheet1

Cell Formulas
Range	Formula
M3:M13	M3	=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:N13	N3	=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:O13	O3	=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]

Press CTRL+SHIFT+ENTER to enter array formulas.
A2 42 or 12

Invoice Pull.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table A				Table B								Table C
2	Part No	Sum of Outstanding GRN Qty			GRN Number	GRN Line	Part No	CPO Num	CPO Line	Confirmed Receipt ETA	Outstanding GRN Qty		GRN Number	GRN Line	Outstanding GRN Qty
3	A1	3			84513892	19	A1	43	30	12/4/2020	3		84513892	19	3
4	A2	12			84513894	18	A1	44	50	12/4/2020	6		84513894	17	3
5	A3	40			84513894	17	A1	45	90	12/4/2020	3		84514036	8	3
6					84513932	36	A1	46	40	12/10/2020	6		84513932	37	12
7					84514036	8	A1	47	50	1/8/2021	3		84513947	5	40
8					84513932	19	A2	48	140	12/10/2020	69
9					84513932	24	A2	49	150	12/10/2020	33
10					84513932	37	A2	50	140	12/10/2020	12
11					84513971	6	A2	51	170	12/23/2020	30
12					84513971	37	A2	52	150	12/23/2020	120
13					84513988	13	A2	53	130	12/29/2020	21
14					84514036	28	A2	54	180	1/8/2021	60
15					84513947	38	A3	55	320	12/17/2020	10
16					84513947	2	A3	56	200	12/17/2020	10
17					84513947	5	A3	57	40	12/17/2020	40
18					84514014	55	A3	58	50	1/4/2021	5
19					84514014	1	A3	59	320	1/4/2021	50
20					84514014	19	A3	60	330	1/4/2021	10
21					84514014	18	A3	61	330	1/4/2021	35
ورقة1

Cell Formulas
Range	Formula
M3:M21	M3	=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:N21	N3	=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:O21	O3	=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:A16	A3	=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]

correction

المصنف1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Table A				Table B								Table C
2	Part No	Sum of Outstanding GRN Qty			GRN Number	GRN Line	Part No	CPO Num	CPO Line	Confirmed Receipt ETA	Outstanding GRN Qty		GRN Number	GRN Line	Outstanding GRN Qty
3	A1	3			84513892	19	A1	43	30	12/4/2020	3		84513892	19	3
4	A2	42			84513894	18	A1	44	50	12/4/2020	6		84513894	17	3
5	A3	40			84513894	17	A1	45	90	12/4/2020	3		84514036	8	3
6					84513932	36	A1	46	40	12/10/2020	6		84513947	5	40
7					84514036	8	A1	47	50	1/8/2021	3
8					84513932	19	A2	48	140	12/10/2020	69
9					84513932	24	A2	49	150	12/10/2020	33
10					84513932	37	A2	50	140	12/10/2020	12
11					84513971	6	A2	51	170	12/23/2020	30
12					84513971	37	A2	52	150	12/23/2020	120
13					84513988	13	A2	53	130	12/29/2020	21
14					84514036	28	A2	54	180	1/8/2021	60
15					84513947	38	A3	55	320	12/17/2020	10
16					84513947	2	A3	56	200	12/17/2020	10
17					84513947	5	A3	57	40	12/17/2020	40
18					84514014	55	A3	58	50	1/4/2021	5
19					84514014	1	A3	59	320	1/4/2021	50
20					84514014	19	A3	60	330	1/4/2021	10
21					84514014	18	A3	61	330	1/4/2021	35
ورقة1

Cell Formulas
Range	Formula
M3:M21	M3	=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:N21	N3	=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:O21	O3	=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:A16	A3	=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]

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!!