I have an Excel sheet with sales data for this year, with each row referring to a transaction. There are 47 columns but the important ones for my purposes are part code, unit price and date.
What I want is to be able to identify whether a particular transaction was the most recent for that particular part code. Example data below:
A B C D 1 partcode price date 2 red-one £50.00 01/06/2016 3 blue-two £4.50 01/11/2016 4 green-three £1.00 20/10/2016 5 red-one £45.00 01/11/2016 6 red-one £47.00 02/11/2016 7 blue-two £5.00 20/05/2016 8 yellow-four £31.50 30/10/2016 9 yellow-four £31.00 01/10/2016
So my end goal is to be able to identify that the most recent price for red-one was £47, for blue-two £4.50, green-three £1 and yellow-four £31.50. I thought I might be able to do this be creating a helper column called Most Recent that would return TRUE in rows 2, 3, 5, and 7. I could then filter that in a Pivot Table quite easily. But I cannot figure out how to do those two things at once in one formula (i.e. subset by part code and check whether the date is the max in the range).