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!!

 

[nbendotron]

I have created a workaround to make your formula work, Aladin. On my data table I created a new column with the formula =COUNTIF(AJ12,"*exit*"). This formula spits out a 1 if it has an exit and a 0 if it does not. Then I went to my output tab and wrote this formula based on what you had created, =IFERROR(1/(1/MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,IF('data.Details of Settlements'!$C$12:$C$10000>0.5,'data.Details of Settlements'!$AM$12:$AM$10000)))),"")

The, IF('data.Details of Settlements'!$C$12:$C$10000>0.5, takes care of the "exit" portion.

Thank you Aladin and dspel for your help

 

[Aladin Akyurek]

I have created a workaround to make your formula work, Aladin. On my data table I created a new column with the formula =COUNTIF(AJ12,"*exit*"). This formula spits out a 1 if it has an exit and a 0 if it does not. Then I went to my output tab and wrote this formula based on what you had created, =IFERROR(1/(1/MAX(IF($A1106='data.Details of Settlements'!$A$12:$A$10000,IF('data.Details of Settlements'!$C$12:$C$10000>0.5,'data.Details of Settlements'!$AM$12:$AM$10000)))),"")

The, IF('data.Details of Settlements'!$C$12:$C$10000>0.5, takes care of the "exit" portion.

Thank you Aladin and dspel for your help

That's great. IsNumber/Search should work though. Thanks for providing feedback.

 

[nbendotron]

dspel, or anyone else, is there a way to make your column c formula work if the names are not grouped together? this formula is perfect because I am trying to pull out the names of unique id's into a formula exactly like you are doing in column c, but the data is not always going to be grouped and we are trying to eliminate any sorting of the data for future users.

this is in reference to an earlier post shown below. I do not know how to reply with that nifty quote box that Aladin is using..



Re: Pulling the most recent date help!!

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="class: cms_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]
[/TR]
</tbody>[/TABLE]

​