Lookup data from Old entries to latest

nabeelahmed

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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
LTUACAD
3Description of spare partsreceived date Tag #Balance on HandUnit Price
4Bechem Berulub VPN 13 1-Oct-20195737105000
5EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira1-Oct-201985795
6Cylindrical Roller Bearing NU306 ECP SKF1-Oct-201974152
7Bearing 7306 BECEP SKF1-Oct-201974163
8Shaft OD 34x380 mm L SS Fabricated as per Sample1-Oct-2019DI38671
9Bechem Berulub VPN 13-11-Sep-20205737144950
10EPTFE Gasket 3mm 1" 150lbs RF ANSI B16.21 Spira1-Sep-2020857947
11PTFE Gore Ring Gasket 1.5" x 3mm #1501-Sep-2020200625
12Bearing 7306 BECEP SKF1-Sep-2020741622
13Shaft OD 34x380 mm L SS Fabricated as per Sample1-Sep-2020DI386711
14Bechem Berulub VPN 13-23-Sep-2020573754700
Tools & Consumables

Spares & Consumable Register.xlsx
DEFGHM
5DateTag #Item DescriptionUOMQty IssuedUnit Price
65-Sep-20205737Bechem Berulub VPN 13 Drum1
7 
8 
9 
10 
11 
12 
13 
Issuance
 

Some videos you may like

Excel Facts

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

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,974
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

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 29, 2006
Messages
1,974
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

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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...
1599297788633.png

1599297809585.png

1599297827794.png


Thanks
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,974
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
VBA Code:
=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
Code:
=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

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
VBA Code:
=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
Code:
=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

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Jun 19, 2020
Messages
76
Office Version
  1. 365
Platform
  1. Windows
Below are snap shots for reference:
Spares & Consumable Register11.xlsx
LTUABACADAO
3Description of spare partsreceived date Tag #Total Qty RecvdBalance on HandUnit PriceRemark
4Bechem Berulub VPN 13 1-Oct-201957371050001 unit was here which is issued must show this Row unit price
5Bechem Berulub VPN 13-11-Sep-202057371049501 unit was here which is issued must show this Row unit price
6Bechem Berulub VPN 13-23-Sep-202057372144002 Units were here 1 of them is issued must show this Row unit price
Tools & Consumables

Spares & Consumable Register11.xlsx
DEFGHMNT
5DateTag #Item DescriptionUOMQty IssuedUnit PriceTotal PriceHelper
65-Sep-20205737Bechem Berulub VPN 13-2Drum1AED 4,400.00 AED 4,400.00 6
75-Sep-20205737Bechem Berulub VPN 13-2Drum1AED 4,400.00 AED 4,400.00 6
85-Sep-20205737Bechem Berulub VPN 13-2Drum1AED 4,400.00 AED 4,400.00 6
Issuance
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,974
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,664
Messages
5,597,450
Members
414,145
Latest member
lonnie451

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