CODEWITHSUNDEEP
exceldate
Cryssie
Cryssie

Reputation: 3185

Get number of days for the last two dates for every cells with same ID in Excel

I have data like shown below. 

ID          Date
8215    3/11/2013
8215    3/11/2011
8215    3/12/2009
8215    3/10/2007
18150   3/1/2013
18150   3/4/2011
18150   3/4/2009
18150   3/1/2008
18150   3/6/2006

Desired Results:

ID          Num_days
8215        731  
18150       641

I would like to get the number of days for the last two dates for entity with ID 8215. In this example the number of days would 731 and 641 for ID 18150. It's hard to get the number of dates for the last two dates when I have over 70k entities. How can I do this so I can calculate the average later on?

Upvotes: 1

Views: 236

Answers (4)

andy holaday
andy holaday

Reputation: 2302

If you start with a list of unique IDs in column D, this should work. No sorting required:

=LARGE(IF($D2=$A:$A,$B:$B),1)-LARGE(IF($D2=$A:$A,$B:$B),2)

Confirm with Ctrl+Shift+Enter

It will be faster if you limit the ranges $A:$A and $B:$B.

Edit: I interpreted "last two dates" to mean "most recent two dates". Another interpretation might be "most recent two entries in an ordered descending list". In the latter interpretation use this instead:

=SMALL(IF($D2=$A:$A,$B:$B),2)-SMALL(IF($D2=$A:$A,$B:$B),1)

Upvotes: 3

WGS
WGS

Reputation: 14179

This works even without sorting. It's an array formula that gets the two largest dates based on condition and calculates the difference.

=MAX(IF(A:A=8215,B:B))-LARGE(IF(A:A=8215,B:B),2)

This is an array formula so use Ctrl-Shift-Enter when inputting it.

It's better if you have a unique list somewhere, change 8215 to refer to the range, and just drag down.

Screenshot:

enter image description here

Hope this helps.

Upvotes: 0

blackghost
blackghost

Reputation: 1825

Assuming ID is in column A, and dates are in column B, you would create column C, such that row 2 would read:

=(A2=A1) * (A2<>A3) * (B2-B1)

The first part (A2=A1) returns 1 if the ID is the same as the previous row, or 0 otherwise. The next part returns 1 if the ID is not the the same as the next row (so so far you have 1 if you're in the last row of the ID, 0 otherwise), and the last part (B2-B1) gets the number of days between the two dates. This will return 0 or the number of days

Upvotes: 0

pnuts
pnuts

Reputation: 59485

I'm not sure I understand your requirement, but would something like: 

=IF(A2<>A1,B2-B3,"")

copied down to suit serve?

Upvotes: 0

Related Questions