0

I have an Excel spreadsheet with four columns of information.

The first column is the primary key of the record. The second column is the edit date of the Closed Date field. The third column is the old value of the Closed Date field The fourth column is the New Value of the Closed Date field

My need is for a formula that will match the primary key (column A), then look for the earliest date from the edit date (column B, not looking past the matching of the primary key in column A) and then return into column D the value from Old Value (Column D).

As an example: in the below screen shot I would like to scan the three records, then look only at the three edit dates and then return the 10/31/2015 date because it is a match with the 10/22/15 edit date, which is the earliest date of the three records.

Thanks in advance for the help.

enter image description here

Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
JIm
  • 1
  • Possible duplicate: http://stackoverflow.com/questions/11570223/conditional-min-and-max-in-excel-2010 – Ditto Mar 09 '16 at 19:37

2 Answers2

1

Try this Array formula:

=INDEX($D$2:$D$200,MATCH(1,IF(($A$2:$A$200 = "006d000000a3akCAAQ")*(MIN(IF($A$2:$A$200 = "006d000000a3akCAAQ",B$2:$B$200))=B$2:$B$200),1,0),0))

It is an array formula so Ctrl-Shift-Enter to confirm instead of Enter when exiting edit mode.

You can change the long string for the Id to a cell reference.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    Office 365 subscriptions get the [new formulas](https://blogs.office.com/2016/02/23/6-new-excel-functions-that-simplify-your-formula-editing-experience/) for Maxifs and Minifs, which will make scenarios like this a lot easier and remove the need to array enter. – teylyn Mar 09 '16 at 20:11
  • I realize that but could not get rid of the need for a CSE formula because the return was not the min itself but column D, and the needed column A reference. IF we simply did `MATCH(B$2:$B$200,MINIFS(B$2:$B$200,$A$2:$A$200,"006d000000a3akCAAQ"),0)` it could in theory return the wrong date if there are duplicates, which with dates are a strong possibility, so another IF would still be needed and so would the CSE. At least that is the way I see it. As history has proved I am not all knowing in these matters. : ) @teylyn. – Scott Craner Mar 09 '16 at 20:18
  • @ScottCraner Thanks for the quick reply. How can hte formula be adjusted so I can scan the entire column of primary keys without listing a specific key itself? – JIm Mar 09 '16 at 20:32
  • 1
    You don't want to use entire columns with array formulas. At the very least, use only the populated rows. Array formulas are slow with large data sets. – teylyn Mar 09 '16 at 20:35
  • @JIm see Teylyn's comment. You want to increase the range to the smallest needed to cover all the data. But I have a question trying to clarify what you are asking; How are you wanting the output? – Scott Craner Mar 09 '16 at 20:39
  • I have over 60k lines of data that have repeating primary keys. I need to have the formula scan column A for all the matching primary keys, then look at column B for the earliest date for that key, then output the data from column C. – JIm Mar 09 '16 at 20:55
  • @JIm one at a time or you want to have a column full of responses? Are you going to type an Id in one cell somewhere and have the the value returned in another cell, do you want to take another column and copy the formula down referring to the current list. Either way you replace `"006d000000a3akCAAQ"` with the cell reference you want. If you are doing the column then you put in the cell reference from the same row then drag down. But if you are dragging this formula for 60k lines you will quickly bog down excel to crawl. vba would be better. – Scott Craner Mar 09 '16 at 21:02
0

With 60k rows, you may want to look into an alternative to the array formula. How about a pivot table. Drag ID and Old Value into the rows area, drag date into the Values and set it to display Min. Set a value filter on the Old Value to show the Top 10 and configure it to show the bottom 1.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73