Last Purchase Qty and Cost

DoryRules

New Member
Joined
Mar 29, 2009
Messages
5
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
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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
Back
Top