How to get the latest data from a workbook

[Vikram Dhemare]

Hi,
I usually download the receipt data from oracle database in excel
spreadsheet say Workbook 1.
At present the data is almost about 40000 rows & will be increased as the
days passes. It is the data of goods received from 1st April to Till date.
The database Fields are like
Col.A Col.B Col.C Col.D Col.E
Goods Recepit Note No. Recpt Date Item Code Recd. Qty Unit Rate
Col.F Col.G Col.H
Basic Value Grand Total Supplier Name
I have another worksheet which contents master item list say Workbook 2.
The Master Item List - Fields are like
Col. A Col.B Col.C
Item Code Description Basic Rate(answer would be latest purchased unit
rate from workbook1)
Col.D Col.E
Rate as on (answer would be GRN Date) Ref. No. (answer would be GRN No.)
Col. F
Cumulative Basic Value (The Answer would be running total of Basic Value
field as on latest date)
If any particular Item Code has not been purchased on any particular date
the answers would be get from the
previous / last receipt date for that particular item and so on.
The List of Items is almost 500 rows.
Now I wish the pull the latest data from workbook 1 in resp. col. of C,D,E,F,G
The dat or values in Col.A & Col.B of workbook2 are fixed.
Could anybody write the code to get the desired results.
Thanks in advance.

 

[Carim]

Hi,

LastRow = Cells(Cells.Rows.Count,"A").End(xlUp).Row
would give you the integer representing the last row used in column A

LastRowValue = Cells(LastRow,1).Value
would give you the Value of the last cell in column A ( 1 represents
first column, i.e A )

HTH
Cheers
Carim

 

[Vikram Dhemare]

Hi Carim,

Actually I do not want the last row value or cell value, I want to retrieve
the data from latest or last updated data.
Say - If Item 'A' being purchased on 13/10/2006 at $1500 /-
and the same item purchased on previous date (i.e 12/10/2006) at $ 1600/-
In this case, the result in master workbook should be

Item description Rate Rate as on
A Matl. $1500/- 13/10/2006 (i.e. the data from latest
purchased date).

Items & Description columns are the fixed or say master list & col. c
onwards should be the desired answers.
Hope this make sense.

 

[Carim]

Hi,

Have a go with following array formula : (to be entered with
Control+Shift+Enter)

=MAX(IF(($A$1:$A$4000=A1),$D$1:$D$4000))

Provided your items in column A and your dates in column D

HTH
Cheers
Carim