Pulling the most recent date help!!

[nbendotron]

Hey guys,

First time post, but I have read a few posts and it looks like this board is incredibly helpful. I am trying to create a formula that will pull the most recent date given a specific ID. The snag is that there are several dates associated with each ID. I have built a formula that identifies which ID's have multiple dates, and for the ID's with a single date it pulls that specific date. I am stuck because I do not know how to plug in a formula that will pull the most recent date for the ID's that have multiple dates. The formula is below..

=IFERROR(IF($B5="","",IF(COUNTIFS('data.Details of Settlements'!$A$12:$A$1048576,$A5,'data.Details of Settlements'!$AK$12:$AK$1048576,">0.5")>1,"00000",IF(VLOOKUP($A5,'data.Details of Settlements'!$A$12:$AK$1048576,37,FALSE)>0.5,VLOOKUP($A5,'data.Details of Settlements'!$A$12:$AK$1048576,37,FALSE),""))),"")

The "00000" entry is just a placeholder for where this max date function should go. Any help is greatly appreciated!!

 

[dspel79082]

Hi nbendotron, I didn't attempt to write this into your formula, I'll leave that to you. But what you will need is an array formula to find your max date. I have a second name column in the example, if all your names are grouped together, you can put the formula from column C inside the formula in column D. C is just to assign the name to the date. Here is the example

column C formula for the following example =IF((COUNTIF($B$2:B2,B2)=1)*1=1,B2,"")

column D formula for the following example =IF(IF((COUNTIF($B$2:B2,B2)=1)*1=1,B2,"")="","",MAX(IF(B2:B14=IF((COUNTIF($B$2:B2,B2)=1)*1=1,B2,""),A2:A14,"")))

**column D formula is an array formula, so ctrl+shift+enter instead of enter
drag formulas down the rows
[TABLE="width: 343"]
<tbody>[TR]
[TD]9/13/2012
[/TD]
[TD]john
[/TD]
[TD]john
[/TD]
[TD="align: right"]10/10/2012
[/TD]
[/TR]
[TR]
[TD]10/10/2012
[/TD]
[TD]john
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/13/2012
[/TD]
[TD]john
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10/2012
[/TD]
[TD]mo
[/TD]
[TD]mo
[/TD]
[TD="align: right"]10/10/2012
[/TD]
[/TR]
[TR]
[TD]9/13/2012
[/TD]
[TD]mo
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10/2012
[/TD]
[TD]bo
[/TD]
[TD]bo
[/TD]
[TD="align: right"]10/10/2012
[/TD]
[/TR]
[TR]
[TD]10/10/2012
[/TD]
[TD]mo
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/13/2012
[/TD]
[TD]bo
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/10/2012
[/TD]
[TD]ed
[/TD]
[TD]ed
[/TD]
[TD="align: right"]10/10/2012
[/TD]
[/TR]
[TR]
[TD]9/23/2012
[/TD]
[TD]ed
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9/23/2012
[/TD]
[TD]sam
[/TD]
[TD]sam
[/TD]
[TD="align: right"]9/23/2012
[/TD]
[/TR]
[TR]
[TD]9/23/2012
[/TD]
[TD]frank
[/TD]
[TD]frank
[/TD]
[TD="align: right"]9/23/2012
[/TD]
[/TR]
[TR]
[TD]7/16/2012
[/TD]
[TD]frank
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[nbendotron]

Hi dspel, thanks for your response. I cannot get your formula to work and I think it is because I do not understand what you are referencing in this portion of the formula, COUNTIF($B$2:B2,B2). Should that be (B2:B14,B2)?

 

[Aladin Akyurek]

Hey guys,

First time post, but I have read a few posts and it looks like this board is incredibly helpful. I am trying to create a formula that will pull the most recent date given a specific ID. The snag is that there are several dates associated with each ID. I have built a formula that identifies which ID's have multiple dates, and for the ID's with a single date it pulls that specific date. I am stuck because I do not know how to plug in a formula that will pull the most recent date for the ID's that have multiple dates.

...

Shouldn' that be:

=MAX(IF(IdRange=Id,DateRange))

which must be confirmed with control+shift+enter, not just enter?

 

[nbendotron]

I could not get this formula to work, but another guy that works with me came up with the following formula in case anyone ever has the same problem..

=IF(MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,'data.Details of Settlements'!$AL$12:$AL$10000))=0,"",MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,'data.Details of Settlements'!$AL$12:$AL$10000)))

Where A1106 is the individual ID, A12:A10000 is the range of identifiers on the data tab, and AL12:AL10000 is the range of dates. Let me know if anyone sees any problems with this formula, but it seems like it is working.

Now I am trying to add another condition to this formula that will only pull dates with accounting classes that have the word "exit" in the accounting class name. The word can appear anywhere in the name, so it is not just the left 4 letters or anything like that. Does anyone know how to add this criteria to this equation?

 

[Aladin Akyurek]

I could not get this formula to work, but another guy that works with me came up with the following formula in case anyone ever has the same problem..

=IF(MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,'data.Details of Settlements'!$AL$12:$AL$10000))=0,"",MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,'data.Details of Settlements'!$AL$12:$AL$10000)))

Where A1106 is the individual ID, A12:A10000 is the range of identifiers on the data tab, and AL12:AL10000 is the range of dates. Let me know if anyone sees any problems with this formula, but it seems like it is working.

Now I am trying to add another condition to this formula that will only pull dates with accounting classes that have the word "exit" in the accounting class name. The word can appear anywhere in the name, so it is not just the left 4 letters or anything like that. Does anyone know how to add this criteria to this equation?

Care to state the ranges involved: the range for accounting classes and the range where the word exit and like show up?

 

[nbendotron]

The range that is needed to be searched for accounting classes that contain the word "exit" is $AI$12:$AI$10000. This range includes all of the accounting classes and I need to find the ones that contain "exit"

Thank you for your help

 

[Aladin Akyurek]

The range that is needed to be searched for accounting classes that contain the word "exit" is $AI$12:$AI$10000. This range includes all of the accounting classes and I need to find the ones that contain "exit"

Thank you for your help

Control+shift+enter, not just enter:

=IFERROR(1/(1/MAX( IF($A1106='data.Details of Settlements'!$A$12:$A$10000,
  IF('data.Details of Settlements'!$AI$12:$AI$10000="exit",
   'data.Details of Settlements'!$AL$12:$AL$10000)))),"")

This requires a 2007 system or later.

 

[nbendotron]

Thank you Aladin for checking into this. Unfortunately, that formula is not working and I think it has to do with the "exit" criteria. The accounting class column does not have classes that contain only the word "exit". It has classes that say something like "loan exit proceeds" or "exit costs", so the word exit is embedded amongst other words. Do you know of a way to incorporate this into the formula?

Thanks again

 

[Aladin Akyurek]

Thank you Aladin for checking into this. Unfortunately, that formula is not working and I think it has to do with the "exit" criteria. The accounting class column does not have classes that contain only the word "exit". It has classes that say something like "loan exit proceeds" or "exit costs", so the word exit is embedded amongst other words. Do you know of a way to incorporate this into the formula?

Thanks again

Control+shift+enter, not just enter:

=IFERROR(
  1/(1/MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,
   IF(ISNUMBER(SEARCH("exit",'data.Details of Settlements'!$AI$12:$AI$10000)),
    'data.Details of Settlements'!$AL$12:$AL$10000)))),"")