Excel Formula to get values old to latest

[nabeelahmed]

Dear Friends, I am trying to do following where i need your help. Please help me to do below tasks

1- When I enter Customer number in sheet1 (Column"A") old PO number for the same customer with balance quantity from Sheet2(Column"B") should appear in Sheet1(Column"B)
2- When the quantity of old PO finished(Zero) then next PO number for the same customer id should should appear
3- I want PO balance for current PO in Sheet1(Column"B") once quantity issued as described in sheet1(Column"C")

Thanks & Regards,

Nabeel

 

[CA_Punit]

You have not copied the formula entirely. The Core Point of the formula was PO# number. The PO Number is supposed to be generated automatically and priority is given to PO number generated first (Considering Row 1 being First and Row 10 being last ). So please help me to understand what is your extra requirement considering my formula.

Lets discuss considering what changes you require and what is the methodology of entering data (What should be automatted and what will be entered manually)

 

[nabeelahmed]

Dear what formula you have provided is perfectly working on that workbook but when i apply on my actual working sheet its not working there maybe there is some mistake from my side while entering formula, can you please check attached and advise me where i am wrong??

Delivery Note Register.xlsm
	A						G	H			K																			AD												AP	AQ
5	Sr#						PO #	PO Balance			Customer ID																			Qty Issued												Helper	PO Order qty Opening
6	1										000-2020-5000045																			50												000-2020-5000045
7	2										235-2019-1000235																			2.98												235-2019-1000235
8	3										000-2015-5000011																			75.55												000-2015-5000011
9	4										000-2015-5000011																			74.52												000-2015-5000011	0
10	5										000-2020-5000045																			77.22												000-2020-5000045	0
11	6										000-2020-5000045																			25.02												000-2020-5000045	0
12	7										000-2020-5000045																			78.83												000-2020-5000045	0
13	8										000-2020-5000045																			75.52												000-2020-5000045	0
14	9										000-2020-5000045																			73.57												000-2020-5000045	0
15	10										000-2015-5000011																			75.64												000-2015-5000011	PO Balance
16	11										000-2015-5000011																			24.52												000-2015-5000011	0
DATA

Cell Formulas
Range	Formula
AD6:AD16	AD6	=+Y6+AB6
AP6:AP16	AP6	=K6&G6
AQ6:AQ16	AQ6	=IFERROR(IFERROR(INDEX($H$1:H1,AGGREGATE(14,6,1/(1/(IF(K6&G6=$K$5:K5&$G$5:G5,ROW($K$5:K5)-ROW($K$5)+1))),1)),VLOOKUP(K6&G6,IF({1,0},'PO LIST & STATUS'!$K$5:$K$200,'PO LIST & STATUS'!$G$6:$G$200),2,0)),"")
A7:A16	A7	=+IF(E7>0,A6+1," ")
G6:G16	G6	=IFERROR(INDEX(FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200),IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200)<>FALSE),AGGREGATE(15,6,((SUMIF('PO LIST & STATUS'!$K$6:$K$200,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200),IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200)<>FALSE),'PO LIST & STATUS'!$G$6:$G$200)-SUMIF($AP$5:AP5,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200),IF(K6='PO LIST & STATUS'!$D$6:$D$200,'PO LIST & STATUS'!$F$6:$F$200)<>FALSE),$AD$5:AD5))^0)*(ROW('PO LIST & STATUS'!$D$6:$D$200)-ROW('PO LIST & STATUS'!$D$6)+1),1)),"")

Delivery Note Register.xlsm
	D		F	G				K
5	Customer No.		PO No.	PO Quantity (MT)				Helper
6	000-2020-5000045		19200025	5000				000-2020-500004519200025
7	235-2019-1000235		LEG-EMAL2018004-03/18	10000				235-2019-1000235LEG-EMAL2018004-03/18
8	236-2019-1000236		LEG2018011-03/18	0				236-2019-1000236LEG2018011-03/18
9	000-2015-5000006		4500070136	399				000-2015-50000064500070136
10	000-2018-5000028		2019-00034					000-2018-50000282019-00034
11	000-2015-5000007		FPO-987	29.46				000-2015-5000007FPO-987
12	000-2020-5000047		NCF-P0000002456	3034.23				000-2020-5000047NCF-P0000002456
13	000-2015-5000017		7420069	32877.55				000-2015-50000177420069
14	000-2020-5000043		5300006153	2254.47				000-2020-50000435300006153
15	000-2015-5000011		UCF-Agreement					000-2015-5000011UCF-Agreement
16	000-2018-5000026		876	48.94				000-2018-5000026876
17	000-2019-5000035		IBN/7953/2020	20.78				000-2019-5000035IBN/7953/2020
18	000-2018-5000030		PO20-00363	182.88				000-2018-5000030PO20-00363
19	000-2019-5000031		7347	1415.97				000-2019-50000317347
PO LIST & STATUS

Cell Formulas
Range	Formula
D6:D19	D6	=VLOOKUP(C6,'Customer List'!$B$5:$G$100,2,)
K6:K19	K6	=D6&F6

 

[nabeelahmed]

PO Quantity it will show error. So I try correct the

Dear Punit, Hope you will be fine.. i was facing some issues regarding above work sheet, hope you can help me o resolve the issues...
I- I can not select Range more than 22 rows ( $B$2:$B$22 ) if i am increasing range to B200 formula is giving error.
II- I have noticed for one customer number right now it showing the same PO number again which is finished already and not auto picking up next new PO number.. its happening only with one customer right now. ( XL2BB) attached below

Please help me to fix it..

Delivery Note Register(Working) - Copy.xlsm
	K																															AP	AQ	AR	AS	AT
5	Customer ID																															Helper	PO#	PO Order Qty Opening	Qty Issued	PO Balance
50	000-2015-5000006																															000-2015-50000064500070136	4500070136	126	21	105
51	000-2015-5000006																															000-2015-50000064500070136	4500070136	105	21	84
52	000-2018-5000028																															000-2018-50000282019-00034	2019-00034	8851.4	76.06	8775.34
53	000-2015-5000006																															000-2015-50000064500070136	4500070136	84	21	63
54	000-2015-5000006																															000-2015-50000064500070136	4500070136	63	21	42
55	000-2019-5000031																															000-2019-50000317347	7347	1363.02	24.99	1338.03
56	000-2015-5000006																															000-2015-50000064500070136	4500070136	42	21	21
57	000-2015-5000006																															000-2015-50000064500070136	4500070136	21	21	0
58	000-2018-5000030																															000-2018-5000030PO20-00363	PO20-00363	54.45	24.99	29.46
59	000-2018-5000028																															000-2018-50000282019-00034	2019-00034	8775.34	70.25	8705.09
60	000-2018-5000028																															000-2018-50000282019-00034	2019-00034	8705.09	73.74	8631.35
61	000-2018-5000030																															000-2018-5000030PO20-00363	PO20-00363	29.46	24.99	4.47
62	000-2018-5000028																															000-2018-50000282019-00034	2019-00034	8631.35	75.16	8556.19
63	000-2018-5000028																															000-2018-50000282019-00034	2019-00034	8556.19	75	8481.19
64	000-2018-5000030																															000-2018-5000030PO20-00363	PO20-00363	4.47	4.47	6.21725E-15
65	000-2018-5000030																															000-2018-5000030PO20-00363	PO20-00363	6.21725E-15	0	6.21725E-15
DATA

Delivery Note Register(Working) - Copy.xlsm
	D		F	G	H	I		K
5	Customer No.		PO No.	PO Quantity (MT)	PO Quantity Despatched	PO Quantity balance		Helper
6	000-2020-5000045		19200025	0	0.00	0.00		000-2020-500004519200025
7	235-2019-1000235		LEG-EMAL2018004-03/18	0	0.00	0.00		235-2019-1000235LEG-EMAL2018004-03/18
8	236-2019-1000236		LEG2018011-03/18	0	0.00	0.00		236-2019-1000236LEG2018011-03/18
9	000-2015-5000006		4500070136	399	399.00	0.00		000-2015-50000064500070136
10	000-2018-5000028		2019-00034	10000	1518.81	8481.19		000-2018-50000282019-00034
11	000-2015-5000007		FPO-987	29.46	0.00	29.46		000-2015-5000007FPO-987
12	000-2020-5000047		NCF-P0000002456	3034.23	460.62	2573.61		000-2020-5000047NCF-P0000002456
13	000-2015-5000017		7420069	32877.55	0.00	32877.55		000-2015-50000177420069
14	000-2020-5000043		5300006153	2254.47	0.00	2254.47		000-2020-50000435300006153
15	000-2015-5000011		UCF-Agreement	3000	24.52	2975.48		000-2015-5000011UCF-Agreement
16	000-2018-5000026		876	48.94	0.00	48.94		000-2018-5000026876
17	000-2019-5000035		IBN/7953/2020	20.78	0.00	20.78		000-2019-5000035IBN/7953/2020
18	000-2018-5000030		PO20-00363	182.88	178.41	4.47		000-2018-5000030PO20-00363
19	000-2019-5000031		7347	1415.97	77.94	1338.03		000-2019-50000317347
20	000-2020-5000055		56325	120	0.00	120.00		000-2020-500005556325
21	000-2020-5000058		65236	500	0.00	500.00		000-2020-500005865236
22	000-2020-5000060		65412	456	0.00	456.00		000-2020-500006065412
PO LIST & STATUS

 

[CA_Punit]

Yes I forgot responding you..

Sorry for the delay. Thanks for reminding me..I will go through the error and respond you at the earliest.

Thanks again for reminding me

 

[CA_Punit]

Hi can you post a external link of your file. My Excel crashes everytime i post your data in my excel

May be Link of Google Drive or One Drive may be used.

 

[nabeelahmed]

Hi can you post a external link of your file. My Excel crashes everytime i post your data in my excel

May be Link of Google Drive or One Drive may be used.

Hi see if this work

Delivery Note Register(Working) - Copy.xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

 

[CA_Punit]

Sorry for the delayed response.

In PO# Column AQ just replace with this formula

=IFERROR(INDEX(FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),AGGREGATE(15,6,ROUND(((SUMIF('PO LIST & STATUS'!$K$6:$K$30,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),'PO LIST & STATUS'!$G$6:$G$30)-SUMIF($AP$5:AP5,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),$AS$5:AS5))),2)^0*(ROW('PO LIST & STATUS'!$D$6:$D$30)-ROW('PO LIST & STATUS'!$D$6)+1),1)),"")

It was due to Rounding off issue which is solved.
Also 6.21.... which was appearing was due to cell formatted as General. Just format it as Number

 

[nabeelahmed]

Sorry for the delayed response.

In PO# Column AQ just replace with this formula

=IFERROR(INDEX(FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),AGGREGATE(15,6,ROUND(((SUMIF('PO LIST & STATUS'!$K$6:$K$30,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),'PO LIST & STATUS'!$G$6:$G$30)-SUMIF($AP$5:AP5,K6&FILTER(IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30),IF(K6='PO LIST & STATUS'!$D$6:$D$30,'PO LIST & STATUS'!$F$6:$F$30)<>FALSE),$AS$5:AS5))),2)^0*(ROW('PO LIST & STATUS'!$D$6:$D$30)-ROW('PO LIST & STATUS'!$D$6)+1),1)),"")

It was due to Rounding off issue which is solved.
Also 6.21.... which was appearing was due to cell formatted as General. Just format it as Number

Dear yes its perefctly working now... Thank you...

 

[CA_Punit]

Thank you for the feedback.

And hope it works well. Just remember to input the Quanity in 2 decimal only. As i have used Round( ,2) function.

 

[nabeelahmed]

Thank you for the feedback.

And hope it works well. Just remember to input the Quanity in 2 decimal only. As i have used Round( ,2) function.

Yes it is Working very well & Noted your advice.. Thank you so much again for your support...