0

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).

2

I think perhaps you mean "a helper column called Most Recent that would return TRUE in rows 3, 4, 6, and 8", as those are the rows that have the latest date for each partcode.

For your Most Recent helper column, you could use an array formula. One potential problem with this, however, is that array formulas are notoriously slow if they have to process a large number of rows. If that's not an issue for you, try the following:

  1. In cell E2, type the following, but do not press the Enter key to accept it: =MAX(IF($B$2:$B$9=B2,$D$2:$D$9)) = D2
  2. Press Ctrl + Shift + Enter to accept the formula, which will make it an array formula (if this is done correctly, you will then see curly braces surrounding the formula in the formula bar when the cell has the focus)
  3. Formula-copy cell E2 down through cell E9


Here's a screenshot (I'm in the USA, so to avoid ambiguity I formatted the dates as YYYY-MM-DD):

screenshot

  • Thanks for the answer, it worked perfectly. Just for the benefit of anyone else who stumbles across this: I just used this formula on sheets with 15-40k rows, and it worked fine. Took just a few seconds to update. – hamedbh Nov 18 '16 at 9:01

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.