Min/Max dates

[Statto1970]

Hello

I have a spreadsheet
COL A - contains a year
COL B - persons name
COL C - formula counting the number of years for the person named in COL B

What I would like is to enter in
COL D - the earliest year for the person
COL E - the latest year for the person

There can be upto 18 rows for the same person if they played in 18 years. (The names and years are not necessarily sequential)

For example
A - B - C - D - E
2000 - J Smith - 1 - 2000 - 2005
2001 - J Smith - 2 - 2000 - 2005
2002 - J Smith - 3 - 2000 - 2005
2003 - J Smith - 4 - 2000 - 2005
2004 - J Smith - 5 - 2000 - 2005
2005 - J Smith - 6 - 2000 - 2005

Any help would be appreciated.

 

[steve the fish]

Hi. See if these work first:

=MINIFS(A:A,B:B,B1)
=MAXIFS(A:A,B:B,B1)

 

[Statto1970]

Hi. See if these work first:

=MINIFS(A:A,B:B,B1)
=MAXIFS(A:A,B:B,B1)

Thank you but it doesn't take account of any changes in the name column. The dataset has lots of names and I need it to change at each change of name.

 

[steve the fish]

Sorry you have lost me. The formula will give you the max/min of the name in B1 based on the values in column A where the corresponding name in column B is the same as B1. You could pivot the data if you wanted and get the max/mins.

 

[Statto1970]

Sorry you have lost me. The formula will give you the max/min of the name in B1 based on the values in column A where the corresponding name in column B is the same as B1. You could pivot the data if you wanted and get the max/mins.

Sorry your formula works for

A - B - C - D - E
2000 - J Smith - 1 - 2000 - 2005
2000 - A Brown - 1
2001 - J Smith - 2 - 2000 - 2005
2002 - J Smith - 3 - 2000 - 2005
2003 - J Smith - 4 - 2000 - 2005
2004 - J Smith - 5 - 2000 - 2005
2005 - J Smith - 6 - 2000 - 2005

But my actual dataset is more complex

A - B - C - D - E
2000 - J Smith - 1 - 2000 - 2005
2001 - J Smith - 2 - 2000 - 2005
2002 - J Smith - 3 - 2000 - 2005
2003 - J Smith - 4 - 2000 - 2005
2004 - J Smith - 5 - 2000 - 2005
2005 - J Smith - 6 - 2000 - 2005

 

[Statto1970]

Sorry you have lost me. The formula will give you the max/min of the name in B1 based on the values in column A where the corresponding name in column B is the same as B1. You could pivot the data if you wanted and get the max/mins.

Sorry your formula works for my original dataset as it only contained one name and therefore it min/maxed correctly
But my actual dataset contains many names and using your formula they all have the same (min) date and same (max) date - it doesn't recognise the change of name and their different dates.

A - B - C - D - E
2000 - J Smith - 1 - 2000 - 2005
2000 - A Brown - 1 - 2000 - 2000
2001 - J Smith - 2 - 2000 - 2005
2002 - J Smith - 3 - 2000 - 2005
2003 - T Brown - 1 - 2003 - 2006
2003 - J Smith - 4 - 2000 - 2005
2003 - A Person - 1 - 2003 - 2005
2004 - T Brown - 2 - 2003 - 2006
2004 - A Person - 2 - 2003 - 2005
2004 - T Brown - 3 - 2003 - 2006
2005 - T Brown - 4 - 2003 - 2006
2006 - T Brown - 5 - 2003 - 2006
2004 - J Smith - 5 - 2000 - 2005
2005 - J Smith - 6 - 2000 - 2005
2005 - A Person - 2 - 2003 - 2005

How do I get a formula to recognise the change of names and the different dates allocated to them?

Thank you.

 

[steve the fish]

Sorry it looks to me like 'J Smith' has a min date of 2000 and a max date of 2005. What is the problem you are having because i cant see what you mean?

 

[Statto1970]

{The above is what I want to happen, not what is actually happening}

He does but so does everyone else as it doesn't recognise a change of name in Col B, so every entry picks up the earliest and latest date, irrespective of the person's actual dates. I don't think I can make it much easier to understand.

 

[steve the fish]

Id agree with you if that is what happens. It doesnt on my machine. The result is:

[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]2000[/TD]
[TD="class: xl65, width: 64"]J Smith[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]2000[/TD]
[TD="class: xl65, width: 64"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]A Brown[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2000[/TD]
[/TR]
[TR]
[TD="class: xl65"]2001[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2002[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2006[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
</tbody>[/TABLE]

which looks correct to me. What is the formula you are using in D1 and E1?

 

[Statto1970]

Id agree with you if that is what happens. It doesnt on my machine. The result is:

[TABLE="width: 320"]
<colgroup><col style="width:48pt" width="64" span="5"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]2000[/TD]
[TD="class: xl65, width: 64"]J Smith[/TD]
[TD="class: xl65, width: 64"]1[/TD]
[TD="class: xl65, width: 64"]2000[/TD]
[TD="class: xl65, width: 64"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]A Brown[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2000[/TD]
[/TR]
[TR]
[TD="class: xl65"]2001[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2002[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2006[/TD]
[TD="class: xl65"]T Brown[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2006[/TD]
[/TR]
[TR]
[TD="class: xl65"]2004[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]J Smith[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2000[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
[TR]
[TD="class: xl65"]2005[/TD]
[TD="class: xl65"]A Person[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]2003[/TD]
[TD="class: xl65"]2005[/TD]
[/TR]
</tbody>[/TABLE]

which looks correct to me. What is the formula you are using in D1 and E1?

The one you posted.