Minimum and Maximum Dates

[Andy D 2002]

Hi i have 2 columns

Column A = Clients, Client B = dates

E.g.
Col A Date
****** ******
Client 1 01/01/2000
Client 1 01/02/2000
Client 1 01/03/2000
etc
Client 2 01/07/2000
Client 2 01/08/2001
Client 2 01/09/2002
Client 2 01/10/2002
Client 2 01/02/2003
Client 3 01/01/2000
etc
etc

question: what steps should i go through to work out which the earliest and latest date for each client?

[Mudface]

You could set up a pivot table (see the Mr Excel main website for some examples), and have as your data field a maximum date for each client.

[Colo]

Microsoft Excel - Minimum and Maximum Dates.xls ___Running: xl97 : OS = Windows (32-bit) 5.00
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
D8 = press Ctrl + Shift + Enter same time.
	A	B	C	D	E
1	Clients	Date		Answer
2	Client 1	01/01/2000		latest of Client1	:alert('{=MAX(IF($A$2:$A$9=%22Client%201%22,$B$2:$B$9)%20)}')>01/07/2000
3	Client 1	01/02/2000		earliest of Client1	:alert('=MIN(IF($A$2:$A$9=%22Client%201%22,$B$2:$B$9))')>01/01/2000
4	Client 1	01/03/2000
5	Client 1	01/07/2000		latest of Client2	:alert('{=MAX(IF($A$2:$A$9=%22Client%202%22,$B$2:$B$9)%20)}')>01/02/2003
6	Client 2	01/08/2001		earliest of Client2	:alert('{=MIN(IF($A$5:$A$12=%22Client%202%22,$B$5:$B$12))}')>01/08/2001
7	Client 2	01/09/2001
8	Client 2	01/10/2001		press Ctrl + Shift + Enter same time.
9	Client 2	01/02/2003
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo


Please try this too!

[Aladin Akyurek]

Column A = Clients, Client B = dates

E.g.
Col A Date
****** ******
Client 1 01/01/2000
Client 1 01/02/2000
Client 1 01/03/2000
etc
Client 2 01/07/2000
Client 2 01/08/2001
Client 2 01/09/2002
Client 2 01/10/2002
Client 2 01/02/2003
Client 3 01/01/2000
etc
etc

Andy,

A question: Your sample shows as if the data is sorted on clients and dates per client. Does this sort order really hold for your real data?

Aladin