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
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: