Last Purchase Qty and Cost

[DoryRules]

I've got an automated spreadsheet that receives purchase trx information with the following format(there's other columns, but these are the main). I create a pivot table that I use to populate another table with average cost and other data.
I need to be able to display the date of last purchase along with the cost in this secondary table, and I can't figure out how to get it!! Hopefully someone can show me the way.
Data:
<TABLE>
<TBODY>
<TR><TD>Item</TD><TD>Site</TD><TD>Date</TD><TD>Qty</TD><TD>Unit Cost</TD></TR>
<TR><TD>ProdA</TD><TD>Main</TD><TD>03Mar09</TD><TD>1,000</TD><TD>0.05</TD></TR>
<TR><TD>ProdA</TD><TD>Main</TD><TD>10Mar09</TD><TD>5,000</TD><TD>0.08</TD></TR>
<TR><TD>ProdA</TD><TD>Mill</TD><TD>05Mar09</TD><TD>1,000</TD><TD>0.06</TD></TR>
<TR><TD>ProdA</TD><TD>Mill</TD><TD>09Mar09</TD><TD>2,000</TD><TD>0.07</TD></TR>
<TR><TD>ProdB</TD><TD>Main</TD><TD>03Mar09</TD><TD>2,000</TD><TD>0.07</TD></TR>
<TR><TD>ProdB</TD><TD>Main</TD><TD>04Mar09</TD><TD>2,000</TD><TD>0.07</TD></TR>
<TR><TD>ProdB</TD><TD>Mill</TD><TD>05Mar09</TD><TD>2,000</TD><TD>0.07</TD></TR>
<TR><TD>ProdB</TD><TD>Mill</TD><TD>09Mar09</TD><TD>2,000</TD><TD>0.07</TD></TR>

</TBODY>

</TABLE>

This data is coming from an ERP system, and the spreadsheet opens and the vba runs that creates the pivot table and the secondary table.
What would the process be to get the date and cost of the last purchase for each product at each site? In the data table there will be about 500 products, 5 sites and 3-5 purchases for each combination.(4,000 to 5,000 records)
Thanks in advance!
Bob

 

[SteveO59L]

how would you differentiate between 2 purchases on the same day ?

 

[Mike Blackman]

Hi,

Maybe something like this? I have assumed that your dates are in assending order and are in Column 1;

=INDEX(A:A,MATCH(9.99999999999999E+307,A:A))

MAX might even do it.

=MAX(A:A)

Are there more criteria to your search, ie. Product?

 

[Mike Blackman]

Hi,

Didn't see the bottom part first time i looked, maybe;

=INDEX(A:E,MATCH(MAX(C:C),C:C,0),5)

 

[DoryRules]

Steve059L: There typically would not be multiple transactions for the same product/site combination. If there are, they would have the same cost.

Mike Blackman: I have not used array formulas before.
The raw data is sorted by site, then by item number, then by date. Date is column 3
In this case, the secondary table where I want to show this data is also cleared and rebuilt each time this spreadsheet is run. In VBA, can I programmatically add these formulas to the second table? I know that entering these formulas you need to do a Ctrl+Shift+Enter to tell Excel that it is an array formula.

 

[DoryRules]

Any other suggestions or help?
Bob