开发者

find date interval for unique id excel

I am working with this data in excel. here is the original data

input

person1     2007
person1     2008
person1     2008
person1     2011
person2     2005
person2     2008
person2     2009
person3     2011
person3     2012
person3     2012
person3     2014
person4     2015

whished ouput

person1     2007    0
person1     2008    1   
person1     2008    0
person1     2011    3

person2     2005    0
person2     2008    3
person2     2009    1

person3     2011    0
person3     2012    1
person3     2012    0
person3     2014    2
person4     2015    0

I would like to use an excel f开发者_StackOverflow社区unction to find the year interval for the each person. I have shown my input file and output file. I can't think of anything right now, any ideas?

many thanks in advance.


Here is an array formula that will display the MIN-MAX of each person:

{=MIN(IF($A$2:$A$13=A2,$B$2:$B$13,9999))&"-"& MAX(IF($A$2:$A$13=A2,$B$2:$B$13,1900))}

Need to validate with CtrlShiftEnter

[EDIT] Maybe I was thinking something too hard.

Is that what you want?

=B2-B1

displays the difference between the year of the current row and the previous one - but that's too easy, I must have missed something.

[EDIT 2] Credits to eggplant_parm (cf. comments) - I forgot to reset for each person

=if(a2=a1,b2-b1,0)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜