Returning the last price paid and the date is was bought. THE ANSWER MUST BE OUT THERE!

[bigsi1984]

Hello,

I work in a purchasing function and have a reasonable grasp of excel. However, periodically I need to value stock and the way we do this is to list out the stock items and then put the last price paid and last date purchased next to them.

At the moment I do this manually and I feel like there must be a way to automate a chunk of it.

Sheets involved: Master order sheet (logs all the purchases and the dates) and Stock valuation sheet (lists the stock and needs some info from the Master order sheet)

I've coded all the stock so that I have a unique identifier and I can do a simple vlookup or index match to populate my stoke take valuation sheet from the master ordering sheet. The problem comes because I will purchase the same item multiple times. So really what I need is to return the highest date against a specific item and then in a separate column the rate on that date for that item.

I have been looking at arrays but they are currently beyond me.

Any help would be greatly appreciated.

UPDATE: 2 files attached. I have highlighted the cells that should be interacting.

The Last purchased Tonne Rate on the valuation sheet should be populated from the Tonne Rate cell on the Master order sheet.
The last purchased date on the valuation sheet should be populated from the Date ordered cell on the Master order sheet.
The product code/Short code is the unique element.

The master order sheet can potentially be thousands of lines long come the end of the year.


Thanks.

[JohnTopley]

Please post a sample Excel file (Not image) showing expected results.

To upload file click "Go Advanced" then scroll down to "Manage attachments": "Browse", select file, "Upload" then "Close Window"

[Jonmo1]

It can be done, but we need some actual sheet specific details, ranges etc..

Can you attach a small sample file ?

[aspmacro]

Pl share your samples worksheets

[bigsi1984]

Attachments should be on now. Thanks

[JohnTopley]

in J2

=IFERROR(IF(MATCH($N2,'Master order sheet.xlsx'!$U$2:$U$400,0),INDEX('Master order sheet.xlsx'!$S$2:$S$400,MATCH(MAX(('Master order sheet.xlsx'!$U$2:$U$400=$N2)*('Master order sheet.xlsx'!$D$2:$D$400)),(('Master order sheet.xlsx'!$U$2:$U$400=$N2)*('Master order sheet.xlsx'!$D$2:$D$400)),0)),""),0)


in K2

=IFERROR(IF(MATCH($N2,'Master order sheet.xlsx'!$U$2:$U$400,0),INDEX('Master order sheet.xlsx'!$D$2:$D$400,MATCH(MAX(('Master order sheet.xlsx'!$U$2:$U$400=$N2)*('Master order sheet.xlsx'!$D$2:$D$400)),(('Master order sheet.xlsx'!$U$2:$U$400=$N2)*('Master order sheet.xlsx'!$D$2:$D$400)),0)),""),"")

enter BOTH with Ctrl+Shift+Enter then copy (drag) down columns.

Many product codes are not on the Master.

[bigsi1984]

Works perfectly. Thanks!