vLookup max/min & difference

[adriles]

Hi all,

I have tried everywhere to find a solution to this, and have tried many suggestions from other posts to no avail. I'm pulling my hair out trying different things but just cant get anywhere. I'm hoping a kind sole could assist?

EntityID DOB
0001 12/04/1948
0001 12/04/1950
0002 25/11/1953
0002 25/11/1986
0002 25/11/1987
0003 03/07/1975
0004 03/05/1932
0004 05/03/1932
0004 03/05/1971
0004 03/05/1965
0004 05/03/1982
0005 13/08/1961
0005 13/08/1961
0006 21/01/1966

From the above I want to find the number of days difference between the highest and lowest DOB for each entity ID.

I am happy to have a column C detailing MAX and column D detailing MIN and then getting the difference in column E.

FYI - Duplicate entitiy ID's range from 2 rows to 40+ rows.

FYI - 3000 rows of entity ID's and DOB's

FYI - I have a separate sheet that will do a vlookup to this one allowing me to ultimately filter that sheet by 'DOB discrepancy +365 days' (for example).

Any suggestions greatly appreciated.

Adrian

[benishiryo]

hi adriles, welcome to the forum. i've listed a few methods for you in the file. the formulas are using arrays. to do that, you have to confirm with CTRL + SHIFT + ENTER rather than just ENTER. you should see curly brackets surrounding it when done right. just in case you need editing. in column F:H, it's the same formulas but i made it non repetitive. so each difference for Entity ID would just appear once. i also did a pivot

[adriles]

Awesome, thank you.

And a pivot table... *facepalm* (seems so obvious seeing it there in your attachment!)

I think I might have worked this out if I understood why I cant use A:A or B:B in these formulas which is what I was trying to do with all the different methods I found. Can you shed any light on why I can set a huge area $A$2:$A$5000 but not use A:A?

Thank you again,

Adrian

[benishiryo]

i think it might be an Excel 2003 problem. dont have that to try it out. anyway, try avoid using the whole column as reference. especially so with array formulas. hope that helps