finding min and max value in a range of cells defined by a third cell

Hello all

I have found great help here in the past, so I'm trying my luck again...

In Excel 2007 I have a table looking as follows:


year name1 name2
1985 0001 0002
1990 0001 0002
1991 0001 0002
1997 0003 0004
1998 0003 0004
1999 0003 0004
1950 0005 0006
1955 0005 0006
1980 0005 0006


I would like to be able to output the min and max value of year (first and last year) for each pair of names, e.g. in a table like this:


years name1 name2
1985,1991 0001 0002
1997,1999 0003 0004
1950,1980 0005 0006


or like this:


year1 year2 name1 name2
1985 1991 0001 0002
1997 1999 0003 0004
1950 1980 0005 0006


Is there any formula/code to do so?

If anyone can help, please do!

Thanks in advance,
N

Thanks for your quick reply, Dilipandey!

Maybe I am not understanding it right (yet) or perhaps my question wasn't phrased well enough. In the file you sent, do I have to put in the values for cells F2 and G2 manually?
This is what I would like to have Excel doing, actually. So for each combination of names in column B and C I would like to have Excel list the min and max value (for that pair of names, not for the entire column) of column A.

Please let me know if your formula does that and I simply didn't understand it correctly...

Thanks and kind regards,
N

That was the keyword I needed... I am able to do it with a pivot table now. Great.
I am evidentially lacking Excel routine

Thanks for your help,
N