var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Return last row for each part number...
Hiya folks.. I'm trying to figure out an excel only solution to duplicate an access query.
I have a datasource with multiple entries for a given item number. These entries have a date and a quantity attached to them. I want to return the date and quantity for the most recent entry ONLY.
In access I accomplish this like so:
How can I duplicate this behavior in excel and produce a worksheet containing only the records where the "Effective Date" is the most recent for a given part?
SELECT SAFETY_STOCK.Item, _STOCK.[Effective Date], SAFETY_STOCK.UOM, SAFETY_STOCK.Quantity
WHERE (((SAFETY_STOCK.[Effective Date])=DMax("[Effective Date]","SAFETY_STOCK","Item = '" & [Item] & "'")))
ORDER BY SAFETY_STOCK.Item;
The attached Excel file shows a simple way, using formulas, to isolate the most recent dates for part numbers into separate columns.
By sorting the full list by part number and by date, you can identify the last cell for a specific part # (where the following cell does not equal the current cell). The formulas display part # and date for each part's last row.
I spose I didn't think of doing that with formulas... That's an approach I had to take with a seperate projects requiring a bunch of "department codes" to be split into different sheets.
I was hoping there would be something simpler I could teach my end users, but this is probably the best I can get without going into VBA.
Thanks for the suggestion, I didn't think of going that route.