Ranking question

[Cobber2008]

Hi all, i have a spreadsheet such as below. i want to add a column in column that cranks the time as grouped by Date and city. Eg for Ayr on the 2/1/18 the first rows with the earliers time in this case 12:30 gets a 1 in Column D for each cell and for same day on 2/1 at 12:55 is the next value so thats get a 2. This should be done for each date and city. i cant figure out how to do this i suspect its a Countifs thing but couldnt get it to work? Thanks.

​		​
​		​

2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:30:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	AYR	12:55:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
2/01/2018​	BATH	1:00:00 PM​
4/01/2018​	BANGOR-ON-DEE	1:15:00 PM​
4/01/2018​	BANGOR-ON-DEE	1:15:00 PM​
4/01/2018​	BANGOR-ON-DEE	1:15:00 PM​

 

[Peter_SSs]

Does this do what you want?

23 08 20.xlsm
	A	B	C	D
1
2	1/02/2018	AYR	12:30:00 PM	1
3	1/02/2018	AYR	12:30:00 PM	1
4	1/02/2018	AYR	12:30:00 PM	1
5	1/02/2018	AYR	12:30:00 PM	1
6	1/02/2018	AYR	12:30:00 PM	1
7	1/02/2018	AYR	12:30:00 PM	1
8	1/02/2018	AYR	12:30:00 PM	1
9	1/02/2018	AYR	12:30:00 PM	1
10	1/02/2018	AYR	12:55:00 PM	2
11	1/02/2018	AYR	12:55:00 PM	2
12	1/02/2018	AYR	12:55:00 PM	2
13	1/02/2018	AYR	12:55:00 PM	2
14	1/02/2018	AYR	12:55:00 PM	2
15	1/02/2018	AYR	12:55:00 PM	2
16	1/02/2018	AYR	12:55:00 PM	2
17	1/02/2018	BATH	1:00:00 PM	1
18	1/02/2018	BATH	1:00:00 PM	1
19	1/02/2018	BATH	1:00:00 PM	1
20	1/02/2018	BATH	1:00:00 PM	1
21	1/02/2018	BATH	1:00:00 PM	1
22	1/02/2018	BATH	1:00:00 PM	1
23	1/02/2018	BATH	1:00:00 PM	1
24	1/04/2018	BANGOR-ON-DEE	1:15:00 PM	1
25	1/04/2018	BANGOR-ON-DEE	1:15:00 PM	1
26	1/04/2018	BANGOR-ON-DEE	1:15:00 PM	1
Rank

Cell Formulas
Range	Formula
D2:D26	D2	=MATCH(C2,UNIQUE(FILTER(C$2:C$26,(B$2:B$26=B2)*(A$2:A$26=A2))),0)

 

[Cobber2008]

Excellent yes that works thank you very much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.