Return MAX Date in column per unique ID and also return value of 1 in column where that row contains MAX date per unique ID

[David0227]

I am looking to calculate row C and Row D

In C I want to return the max date per service from column B but it to show for each row of that service
in D I want to return a value of 1 in the row where the MAX date per servive exists

Appreciate any help, thanks

Book1
	A	B	C	D
1	Service	Date	Max Date Service	Row with Max Date Service
2	1	1/24/99	1/25/99
3	1	1/25/99	1/25/99	1
4	2	2/24/98	2/24/98	1
5	3	2/25/98	2/25/98	1
6	4	2/25/98	2/25/98	1
7	5	2/27/98	2/28/98
8	5	2/28/98	2/28/98	1
9	6	3/1/98	3/1/98	1
Sheet1

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D
1	Service	Date	Max Date Service	Row with Max Date Service
2	1	24/01/1999	25/01/1999
3	1	25/01/1999	25/01/1999	1
4	2	24/02/1998	24/02/1998	1
5	3	25/02/1998	25/02/1998	1
6	4	25/02/1998	25/02/1998	1
7	5	27/02/1998	28/02/1998
8	5	28/02/1998	28/02/1998	1
9	6	01/03/1998	01/03/1998	1
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=MAX(IF($A$2:$A$9=A2,$B$2:$B$9))
D2:D9	D2	=IF(B2=C2,1,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[David0227]

Thank you. One additional point i didnt think of was I only want a 1 to return in column d for one of the entries if the dates for a service match. For example, if B2 and B3 both were 25/01/99 id only want a 1 in D3

 

[Fluff]

How about
=IF(AND(B2=C2,COUNTIFS(A$2:A2,A2,B$2:B2,B2)=COUNTIFS(A:A,A2,B:B,B2)),1,"")

 

[David0227]

Wow!!! Thank you so much

 

[Fluff]

You're welcome & thanks for the feedback.

 

[David0227]

I am looking for another layer to this to help.

I only want to include service rows in column C and D calculations if the column B date is before or equal to the service completion date in column E.

For example, row 4 would be excluded from row C and D calculations as the service completion date was the 25th January and B4 is after that date on the 26th

Help is very much appreciated.

Book1
	A	B	C	D	E
1	Service	Date	Max Date Service	Row with Max Date Service	Serrvice Completion Date
2	1	1/24/99	1/25/99		1/25/99
3	1	1/25/99	1/25/99	1	1/25/99
4	1	1/26/99			1/25/99
Sheet1

 

[Fluff]

As this is now a different question, you will need to start a new thread. Thanks.