Using Excel to determine which row to choose

[novodisc]

Hello Excel experts out there,

I have multiple rows of data with same batch number & different end dates.
I want to be able to use Excel assign a weight to the row base on the latest end date.
After that, I want to pivot the data base on the weight assigned to it.

Pls see below for example.

Is there anyway to do this w/o using macro?
If macro is needed, how can i quote the macro?

Your help will be very much appreciated!

Thanks in advance for your response!

Eg:
Batch number Start Date End Date Weight
24689001 25/Mar/07 24/Mar/09 0
24689001 20/Mar/07 24/Mar/09 0
24689001 16/Apr/07 15/Apr/09 0
24689001 25/Mar/09 24/Mar/10 0
24689001 25/Mar/09 7/May/13 5
99987543 25/Mar/07 24/Mar/09 5
43452532 25/Mar/07 24/Mar/09 0
43452532 29/Mar/08 12/Dec/09 0
43452532 25/Mar/09 24/Mar/10 5
53246532 16/Apr/07 15/Apr/09 5
24689051 25/Mar/09 24/Mar/10 5

 

[AlphaFrog]

<br />

Book1
	A	B	C	D
1	Batch number	Start Date	End Date	Weight
2	24689001	25-Mar-07	24-Mar-09	0
3	24689001	20-Mar-07	24-Mar-09	0
4	24689001	16-Apr-07	15-Apr-09	0
5	24689001	25-Mar-09	24-Mar-10	0
6	24689001	25-Mar-09	7-May-13	5
7	99987543	25-Mar-07	24-Mar-09	5
8	43452532	25-Mar-07	24-Mar-09	0
9	43452532	29-Mar-08	12-Dec-09	0
10	43452532	25-Mar-09	24-Mar-10	5
11	53246532	16-Apr-07	15-Apr-09	5
12	24689051	25-Mar-09	24-Mar-10	5
Sheet1

Cell Formulas
Range	Formula
D2	{=(MAX(IF($A$2:$A$12=A2,$C$2:$C$12))=C2)*5}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formula in D2 down column D

Or you could use this formula in D2 if the Batch numbers are always sorted and in chronological order.
=IF(A2=A3,0,5)