Formula to lookup latest revision but data entered prior to current revision should not ch

[GAURAVVOHRA]

I have two sheets:
1) Item database sheet: in this sheet , I maintain all information related to particular items viz. price,description, drawing no., drawing revision no. & revision applicable date etc.
Items are identified by their item codes
2) Po entry sheet:Here i enter po no. item code of item for which po is received, drawing revision mentioned in po etc. Based on the item code data is retreived from item database sheet.

Refer attached file for the layout of two types of sheets mentioned above.
For simplicity, I've removed irrelevant entries.I've also shown the output needed.

Drawings keep on revising.Therefore I've created multiple coloumns for revision data entry in item database sheet & their date of arrival.
Question is, that in PO entry sheet, based on item code, latest revision should be extracted onto the PO entry sheet revision coloumn AND the data which was extracted before the arrival of the new revision, should not be recalculated or disturbed. Old record should keep on maintainig the revision no. that was latest/valid on that particular date of po.

[AliGW]

How do the dates in the second table relate to the dates in the first?

[jason.b75]

Try this in K3 and fill down

Please Login or Register  to view this content.

[GAURAVVOHRA]

Dates in the table2 are dates on which purchase order is received for that particular item code.

Dates in Table1 are the dates on which drawing was revised for that particular item.

Three columns marked rev-A,rev-B,rev-C & their associated date columns were created to fill data as one drawing can have multiple revisions
& different date for each revision.

For any new entry in Table2 for a new po should fetch the latest applicable revision for that item & places in 3rd column of table 2.

To get the latest revision for an item, i created a helper coumn in table1 which contained the latest applicable revision
for that item code by using lookup function & then used index-match to get it in table2, column3.
Problem is that when a new revision comes for an item & lookup extracts the same, then all the records of this item in Table2
get updated even if their po date is prior to the arrival date of the latest revision date.

[AliGW]

Did you see the suggestion offered in post #3?

[jason.b75]

With a large itemcode database my suggestion in post #3 might be slow to calculate, if it is, this one may be faster.

Please Login or Register  to view this content.

[GAURAVVOHRA]

Hello Jason, thanks for the reply.
I've tried the formula offered by you, it is working ok.
Thank you very much.
In case I face any problem, I'll revert.

[jason.b75]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[GAURAVVOHRA]

Hi Jason, thanks for your prompt reply. Both solutions are working ok. I had few queries regarding limitations of each solution & can we overcome them:
A) Solution-1 =AGGREGATE(14,4,($A$3:$A$5=$I3)*($E$3:$G$5<>"")*($E$3:$G$5<$J3)*$B$3:$D$5,1)
A1) One of the arguments in the formula being used is that we are checking if any cell in the range $E$3:$G$5<>"", in these cells I've applied a formula that as soon as there is an entry in cell range B3: D5, a time stamp is created in E3: G5. I believe this will not be treated as blank by our formula.

B) Solution-2 =MATCH($J3,INDEX($E:$G,MATCH($I3,$A:$A,0),0))-1
B1) One of the key components in working of this formula is that the cell contents in the range B3: D5 are sequential numbers starting from zero. If these are alphabets or any other string, then how can we extract them?

[jason.b75]

With solution 1, if you have a formula that shows either a timestamp, or "" then it should work fine, however it will only show a numeric result. Strings will not be extracted.

With solution 2,

Please Login or Register  to view this content.

Should extract any format.

[GAURAVVOHRA]

Just to clarify my questions in the last post, I've attached one file.
How can we solve it with a formula?

[jason.b75]

Adding another criteria to the aggregate array should do it.

Please Login or Register  to view this content.

Approximately how many rows of data will you have in each sheet of your real workbook?

[GAURAVVOHRA]

Table-1 will eventually have say 5k rows. Table-2 far more than that considering repeat po.

[jason.b75]

In that case, if calculation is slow, then this might help to speed it up.

In P3 and filled down to create a reference to the database row that the item code matches. Adjust the refernce to $A$5000 so that it is adequate for the size of the database.

Please Login or Register  to view this content.

Then to get the applicable price

Please Login or Register  to view this content.

This method means that the array is only processing the 1 row in the database where the item matches, not all 5k rows.