Lookup data from Old entries to latest

[nabeelahmed]

Hi Friends,,, Hope alss well... I am working on book where i have 2 sheets Lookup Data sheet is "Tools & Consumables" and where i want to extract data is"Issuance"
- I want that in sheet "Issuance"when i will enter Tag# 5737 in cell E6 the description in cell F6 & Unite Price in cell M6 should be extracted from data sheet from older entry first with having some balance on hand and when that balance finished then data should be extracted from the next entry and so on...

Thanks & REgards,

Spares & Consumable Register.xlsx
	L								T	U								AC	AD
3	Description of spare parts								received date	Tag #								Balance on Hand	Unit Price
4	Bechem Berulub VPN 13								1-Oct-2019	5737								10	5000
5	EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira								1-Oct-2019	8579								5
6	Cylindrical Roller Bearing NU306 ECP SKF								1-Oct-2019	7415								2
7	Bearing 7306 BECEP SKF								1-Oct-2019	7416								3
8	Shaft OD 34x380 mm L SS Fabricated as per Sample								1-Oct-2019	DI3867								1
9	Bechem Berulub VPN 13-1								1-Sep-2020	5737								14	4950
10	EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira								1-Sep-2020	8579								47
11	PTFE Gore Ring Gasket 1.5" x 3mm #150								1-Sep-2020	2006								25
12	Bearing 7306 BECEP SKF								1-Sep-2020	7416								22
13	Shaft OD 34x380 mm L SS Fabricated as per Sample								1-Sep-2020	DI3867								11
14	Bechem Berulub VPN 13-2								3-Sep-2020	5737								5	4700
Tools & Consumables

Spares & Consumable Register.xlsx
	D	E	F	G	H					M
5	Date	Tag #	Item Description	UOM	Qty Issued					Unit Price
6	5-Sep-2020	5737	Bechem Berulub VPN 13	Drum	1
7
8
9
10
11
12
13
Issuance

 

[Anthony47]

Your profile says that you use Office 365, then you should have availabble the "new" function XLOOKUP that can be used instead of VLOOKUP or HLOOKUP and offer a parameter to select "search direction", i.e. from the top (as usual) of from the bottom (your choice).
The function is available to "Office Insiders" since Jan 2019 and has been made available to the general users early 2020 (Jan 2020, in my case); if you don't have it make sure that Office updates are enabled.

Bye

 

[nabeelahmed]

Your profile says that you use Office 365, then you should have availabble the "new" function XLOOKUP that can be used instead of VLOOKUP or HLOOKUP and offer a parameter to select "search direction", i.e. from the top (as usual) of from the bottom (your choice).
The function is available to "Office Insiders" since Jan 2019 and has been made available to the general users early 2020 (Jan 2020, in my case); if you don't have it make sure that Office updates are enabled.

Bye

Dear Anthony thanks for your response but Xlookup not fulfill my requirement as i have condition here that if the balance quantity of the specific Tag# from old entry is ZERO then data should be extracted from the next date(latest from previous) having balance quantity and when that finished then from the next and so on... Hope you will understand and will guide me better.

Thanks

 

[Anthony47]

I am a bit confused about your requirement; based on the "Tool&Consumable" picture that you attached which price you would need to pick from the table for tag #5737, and wich for tag #7416?

Bye

 

[nabeelahmed]

Dear if you will see in that picture in Row4-9-14 Tag#5737 having description " Bechem Berulub VPN 13 " " Bechem Berulub VPN 13-1" " Bechem Berulub VPN 13-2 " "and unit price is "5000" "4950" "4700" respectively ... Suppose when i will enter Tag# 5737 in Sheet"Issuance" Cell # E6 the description in F6 should be extracted from sheet "Tool&Consumable" from the older entry here the older entry is in Row4 and there is i have 10 qty balance in hand so all data will be extracted from Row4 Including "Unit price" which is in Cell AD4.
and suppose if the balance qty in row4 is ZERO then i want to pick data from the next entry which is in Row4 and when it will be ZERO then from the next entry which is in Row14...

Thanks

 

[Anthony47]

In this case I should suggest you use a helper column on the Issuance sheet; for example, if E6 contains the Tag# then in P6 (the helper column) you insert the formula

=SMALL(IF(('Tag&Consumable'!$U$1:$U$1000&('Tag&Consumable'!$AD1:$AD1000<>0))=(E6&TRUE),ROW('Tag&Consumable'!$U$1:$U$1000),""),1)

This formula is an array formula, but your Excel version (365) supports "Dynamic arrays", so you should not need to enter the formula using Contr-Shift-Enter; but if it seems that the returned value is wrong try confirming the formula using Contr-Shift-Enter, non Enter alone.

Now P6 should point to the row from where we need to extract the relevant information, using for example Index.
Fot the Description, the formula to use in F6 is

=INDEX('Tag&Consumable'!$L$1:$L$1000,P6)

The other formulas have the same structure, you will only adapt the address $L$1:$L$1000 to the right column

If you have more than 1000 lines in Tag&Consumable then exctend the height of the range

The helper column will avoid that the formula set in P6 be repeated in all of the subsequent formulas, thus repeting the same (heavvy) calculation several times.

Bye

 

[nabeelahmed]

In this case I should suggest you use a helper column on the Issuance sheet; for example, if E6 contains the Tag# then in P6 (the helper column) you insert the formula

=SMALL(IF(('Tag&Consumable'!$U$1:$U$1000&('Tag&Consumable'!$AD1:$AD1000<>0))=(E6&TRUE),ROW('Tag&Consumable'!$U$1:$U$1000),""),1)

This formula is an array formula, but your Excel version (365) supports "Dynamic arrays", so you should not need to enter the formula using Contr-Shift-Enter; but if it seems that the returned value is wrong try confirming the formula using Contr-Shift-Enter, non Enter alone.

Now P6 should point to the row from where we need to extract the relevant information, using for example Index.
Fot the Description, the formula to use in F6 is

=INDEX('Tag&Consumable'!$L$1:$L$1000,P6)

The other formulas have the same structure, you will only adapt the address $L$1:$L$1000 to the right column

If you have more than 1000 lines in Tag&Consumable then exctend the height of the range

The helper column will avoid that the formula set in P6 be repeated in all of the subsequent formulas, thus repeting the same (heavvy) calculation several times.

Bye

Hi Anthony,, Hope you will be fine.. Thanks for your help it is working but there is one issue when the quantity remain ZERO in sheet"Tools & Consumable" the Description and Unit price automatically disappear and give the Error which should not be happened even quantity remain ZERO.... Please help on this

 

[nabeelahmed]

Hi Anthony,, Hope you will be fine.. Thanks for your help it is working but there is one issue when the quantity remain ZERO in sheet"Tools & Consumable" the Description and Unit price automatically disappear and give the Error which should not be happened even quantity remain ZERO.... Please help on this

more ever when older quantity is becoming ZERO after issuance then the description and Unit prices are also changing/updating as per latest Description and unit price even for previous entries which should remain same..

 

[nabeelahmed]

Below are snap shots for reference:

Spares & Consumable Register11.xlsx
	L								T	U							AB	AC	AD											AO
3	Description of spare parts								received date	Tag #							Total Qty Recvd	Balance on Hand	Unit Price											Remark
4	Bechem Berulub VPN 13								1-Oct-2019	5737							1	0	5000											1 unit was here which is issued must show this Row unit price
5	Bechem Berulub VPN 13-1								1-Sep-2020	5737							1	0	4950											1 unit was here which is issued must show this Row unit price
6	Bechem Berulub VPN 13-2								3-Sep-2020	5737							2	1	4400											2 Units were here 1 of them is issued must show this Row unit price
Tools & Consumables

Spares & Consumable Register11.xlsx
	D	E	F	G	H					M	N						T
5	Date	Tag #	Item Description	UOM	Qty Issued					Unit Price	Total Price						Helper
6	5-Sep-2020	5737	Bechem Berulub VPN 13-2	Drum	1					AED 4,400.00	AED 4,400.00						6
7	5-Sep-2020	5737	Bechem Berulub VPN 13-2	Drum	1					AED 4,400.00	AED 4,400.00						6
8	5-Sep-2020	5737	Bechem Berulub VPN 13-2	Drum	1					AED 4,400.00	AED 4,400.00						6
Issuance

 

[Anthony47]

In message #5 you wrote:

Dear if you will see in that picture in Row4-9-14 Tag#5737 having description " Bechem Berulub VPN 13 " " Bechem Berulub VPN 13-1" " Bechem Berulub VPN 13-2 " "and unit price is "5000" "4950" "4700" respectively ... Suppose when i will enter Tag# 5737 in Sheet"Issuance" Cell # E6 the description in F6 should be extracted from sheet "Tool&Consumable" from the older entry here the older entry is in Row4 and there is i have 10 qty balance in hand so all data will be extracted from Row4 Including "Unit price" which is in Cell AD4.
and suppose if the balance qty in row4 is ZERO then i want to pick data from the next entry which is in Row4 [I guessed you meant "Row7" (Anthony)] and when it will be ZERO then from the next entry which is in Row14...

There is a column AC named "Balance on Hand" and I interpreted this is the column to check for the "balance" higher than Zero.
So, with reference to your information, I assumed that the formula had to return values from row 4 (because Balance on hand is 10), or from row 7 (if AD4 is Zero and AD7 is >0), or from row 14 (if also AD7 is Zero and A14 is >0), and so on checking all the lines in "Tool&Consumable".
Obviously when no any line is suitable to be picked (balance always Zero) the formula have nothing to pick. But this can be easily corrected using "IFERROR" (=ISERROR(TheFormula,WhatReturnInCaseTheFormulaReturnsAnError)) and inserting a default value to pick if the standard

From your latest message I understand that column AD is not the column to look at for checking the "balance" and new columns are shown (hidden in previous messages); so please explain again how checking for "balance"
In snapshot 1 of your latest message all the rows have the remark "must show this Row unit price"; this is confusing me because you write that both 5000 /4950 /4400 must be shown

more ever when older quantity is becoming ZERO after issuance then the description and Unit prices are also changing/updating as per latest Description and unit price even for previous entries which should remain same..

I cannot decode what you mean... What are "the pevious entries" that should not be modified?

So in general please restate the problem given the confusion I have in mind. Maybe attaching a real file with some demo data (and not only the snapshots) could make things easier to explain. For sharing a file you have to use an indipendent fileshare service, for example at filedropper.com (then publish the link that filedropper will give you after the file upload)

Bye