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]

Can you post data using xl2bb addin. In that way we dont have to type all the data

 

[nabeelahmed]

Can you post data using xl2bb addin. In that way we dont have to type all the data

Book1
	A	B	C	D
4	Customer ID	PO #	Quantity Issued	PO Balance
5	000-2019-5000031	7347	25	1390.97
6	000-2019-5000031	7347	30	1360.97
7	000-2018-5000028	2019-00034	50	1950
8	000-2018-5000028	2019-00034	100	1850
9	000-2018-5000028	2019-00034	120	1730
10	000-2018-5000030	PO20-00363	150
11	000-2018-5000028	2019-00034	200	1530
12	000-2018-5000028	2019-00034	100	1430
13	000-2018-5000028	2019-00034	50	1380
14	000-2018-5000028	2019-00034	30	1350
15	000-2015-5000011	UCF-Agreement	25
16	000-2018-5000028	2019-00034	25	1325
Sheet1

Book1
	A	B	C	D
4	Customer ID	PO No.	PO Quantity (MT)	PO Balance
5	000-2020-5000045	19200025	500
6	235-2019-1000235	LEG-EMAL2018004-03/18	400
7	236-2019-1000236	LEG2018011-03/18	300
8	000-2015-5000006	4500070136	399
9	000-2018-5000028	2019-00034	2000
10	000-2015-5000007	FPO-987	29.46
11	000-2020-5000047	NCF-P0000002456	3034.23
12	000-2015-5000017	7420069	32877.55
13	000-2020-5000043	5300006153	2254.47
14	000-2015-5000011	UCF-Agreement	5000
15	000-2018-5000026	876	48.94
16	000-2019-5000035	IBN/7953/2020	20.78
17	000-2018-5000030	PO20-00363	182.88
18	000-2019-5000031	7347	1415.97
19	000-2020-5000045	19200026	400
20	000-2020-5000045	19200027	300
21	235-2019-1000235	LEG-EMAL2018004-03/18	600
Sheet2

 

[CA_Punit]

Let say for CustomerId 000-2019-5000031 the PO Quantity is 1415.97 so what should happen if Say the Balance Quantity was 1410 and you have issued 20. Can this happen?????

And if this can happen what would you require as you have issued extra say 14.03 Qty

 

[nabeelahmed]

Let say for CustomerId 000-2019-5000031 the PO Quantity is 1415.97 so what should happen if Say the Balance Quantity was 1410 and you have issued 20. Can this happen?????

And if this can happen what would you require as you have issued extra say 14.03 Qty

What i understood you want to ask
i- If we have Po balance 1410 and we will issue 20 out of 1410 then balance should be 1390
ii- if i will issue 14.03 extra than PO quantity 1415.97( That is why i want when i enter customer id then the Po with some quantity even 1 should appear in PO# field and once that particular PO quantity will be Zero then in the PO field nothing should be shown unless we will not add new PO No. with available quantity for the that customer.

 

[CA_Punit]

As per your profile you are using office 365 and have Filter option, so considering this

Balance Quantity.xlsx
	A	B	C	D	E	F
1	Customer ID	Helper Column	PO #	PO Order Quantity Opening	Quantity Issued	PO Balance
2	000-2019-5000031	000-2019-50000317348	7348	100	100	0
3	000-2019-5000031	000-2019-50000317347	7347	200	20	180
4	000-2019-5000031	000-2019-50000317347	7347	180	180	0
5	000-2019-5000031	000-2019-50000317349	7349	300	10	290
6	000-2019-5000031	000-2019-50000317349	7349	290		290
7	000-2018-5000030	000-2018-5000030
8	000-2018-5000028	000-2018-50000282019-00034	2019-00034	2000		2000
9	000-2018-5000028	000-2018-50000282019-00034	2019-00034	2000		2000
10	000-2018-5000028	000-2018-50000282019-00034	2019-00034	2000		2000
11	000-2018-5000028	000-2018-50000282019-00034	2019-00034	2000		2000
12	000-2015-5000011	000-2015-5000011UCF-Agreement	UCF-Agreement	5000		5000
13	000-2018-5000028	000-2018-50000282019-00034	2019-00034	2000		2000
Sheet1

Cell Formulas
Range	Formula
B2:B13	B2	=A2&C2
C2:C13	C2	=IFERROR(INDEX(FILTER(IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18),IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18)<>FALSE),AGGREGATE(15,6,((SUMIF(Sheet2!$E$2:$E$18,A2&FILTER(IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18),IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18)<>FALSE),Sheet2!$C$2:$C$18)-SUMIF($B$1:B1,A2&FILTER(IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18),IF(A2=Sheet2!$A$2:$A$18,Sheet2!$B$2:$B$18)<>FALSE),$E$1:E1))^0)*(ROW(Sheet2!$A$2:$A$18)-ROW(Sheet2!$A$2)+1),1)),"")
D2:D13	D2	=IFERROR(IFERROR(INDEX($F$1:F1,AGGREGATE(14,6,1/(1/(IF(A2&C2=$A$1:A1&$C$1:C1,ROW($A$1:A1)-ROW($A$1)+1))),1)),VLOOKUP(A2&C2,IF({1,0},Sheet2!$E$2:$E$18,Sheet2!$C$2:$C$18),2,0)),"")
F2:F13	F2	=IFERROR(D2-E2,"")

Balance Quantity.xlsx
	A	B	C	D	E
1	Customer ID	PO No.	PO Quantity (MT)	PO Balance	Helper Column
2	000-2020-5000045	19200025	500		000-2020-500004519200025
3	235-2019-1000235	LEG-EMAL2018004-03/18	400		235-2019-1000235LEG-EMAL2018004-03/18
4	236-2019-1000236	LEG2018011-03/18	300		236-2019-1000236LEG2018011-03/18
5	000-2015-5000006	4500070136	399		000-2015-50000064500070136
6	000-2018-5000028	2019-00034	2000		000-2018-50000282019-00034
7	000-2015-5000007	FPO-987	29.46		000-2015-5000007FPO-987
8	000-2020-5000047	NCF-P0000002456	3034.23		000-2020-5000047NCF-P0000002456
9	000-2015-5000017	7420069	32877.55		000-2015-50000177420069
10	000-2020-5000043	5300006153	2254.47		000-2020-50000435300006153
11	000-2015-5000011	UCF-Agreement	5000		000-2015-5000011UCF-Agreement
12	000-2018-5000026	876	48.94		000-2018-5000026876
13	000-2019-5000035	IBN/7953/2020	20.78		000-2019-5000035IBN/7953/2020
14	000-2019-5000031	7348	100		000-2019-50000317348
15	000-2019-5000031	7347	200		000-2019-50000317347
16	000-2019-5000031	7349	300		000-2019-50000317349
17	000-2020-5000045	19200027	300		000-2020-500004519200027
18	235-2019-1000235	LEG-EMAL2018004-03/18	600		235-2019-1000235LEG-EMAL2018004-03/18
Sheet2

Cell Formulas
Range	Formula
E2:E18	E2	=A2&B2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A16	Cell Value	duplicates	text	NO
A14	Cell Value	duplicates	text	NO
A1:A13,A15,A17:A1048576	Cell Value	duplicates	text	NO

 

[CA_Punit]

I have tried to create as per your requirement. There might be some issue, please point out to me, i will try to solve all those issues.

Also i have tried using Helper column. It might not be necessary to create the helper column but i am figuring out the menthod without helper. Mean while please check for further modification

Also i have noticed i issue that your balance Quantity cannot be negative. So you have to be careful with Issued Quantity. If issued Quantity exceeds PO Quantity it will show error. So I try correct the error.

 

[nabeelahmed]

I have tried to create as per your requirement. There might be some issue, please point out to me, i will try to solve all those issues.

Also i have tried using Helper column. It might not be necessary to create the helper column but i am figuring out the menthod without helper. Mean while please check for further modification

Also i have noticed i issue that your balance Quantity cannot be negative. So you have to be careful with Issued Quantity. If issued Quantity exceeds PO Quantity it will show error. So I try correct the error.

Its looking perfect.. Let me use it on my original work sheet then will give you feed back...

Thank you very much for giving your valuable time ...

 

[nabeelahmed]

Dear Punit, I tried the formulas in my sheet but there is error, not working properly. I have attached my sheets, can you please help me???

Delivery Note Register.xlsm
	G	H			K																			AD												AP	AQ
7	PO #	PO Balance			Customer ID																			Qty Issued												Helper	PO Order qty Opening
8	19200025				000-2020-5000045																			50												000-2020-500004519200025	10000
9	LEG-EMAL2018004-03/18				235-2019-1000235																			2.98												235-2019-1000235LEG-EMAL2018004-03/18	0
10	UCF-Agreement				000-2015-5000011																			75.55												000-2015-5000011UCF-Agreement	48.94
11	UCF-Agreement				000-2015-5000011																			74.52												000-2015-5000011UCF-Agreement	0
12	19200025				000-2020-5000045																			77.22												000-2020-500004519200025	0
13	19200025				000-2020-5000045																			25.02												000-2020-500004519200025	0
14	19200026				000-2020-5000045																			78.83												000-2020-500004519200026
15	19200027				000-2020-5000045																			75.52												000-2020-500004519200027
16	19200027				000-2020-5000045																			73.57												000-2020-500004519200027	0
17	UCF-Agreement				000-2015-5000011																			75.64												000-2015-5000011UCF-Agreement	0
18	UCF-Agreement				000-2015-5000011																			24.52												000-2015-5000011UCF-Agreement	0
DATA

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

 

[CA_Punit]

I will look it after 6 pm. Have some office work