0

I have some entries as shown in the image below and I am looking for a formula or VBA code (best to be formula) to do a calculation in column J.

The logic I want is:
Under the condition that values in column D are the same, return the latest day in column I. For example, D2 to D14 are with the same value, so return the latest day from I2 to I14 which is 3/16/17.

Column D is the report number, one report can include several transactions, e.g. if I have 10 transactions in one report, I have 10 lines with the same report number. I am trying to identify the date of the last transaction of this report. With right formula, J2 to J14 should all be 3/16/2017.

Can anyone tell me how to do it?

Attached pic

M--
  • 25,431
  • 8
  • 61
  • 93
emma12345
  • 107
  • 4
  • 10
  • Possible duplicate of [Excel: Find min/max values in a column among those matched from another column](http://stackoverflow.com/questions/20904092/excel-find-min-max-values-in-a-column-among-those-matched-from-another-column) – YowE3K May 17 '17 at 21:23

2 Answers2

2

You can use this array formula at J2 then fill down:

=MAX(I2:I1000*(D$2:D$1000=D2))    Ctrl+Shift+Enter

p.s.: it's an array formula, type it then press Ctrl+Shift+Enter

A.S.H
  • 29,101
  • 5
  • 23
  • 50
0

Assuming that column I will always be ascending within the groups of column D (e.g., Sort By Report Key, then by Transaction Date), add this formula to J2 and fill down:

=IF(D2=D3,J3,I2)
HaveSpacesuit
  • 3,572
  • 6
  • 40
  • 59