Ranking/Sequence Challenge

[mikebecker]

I have data that I can't sort and need to determine the chronological sequence for both date and time for every change in "Rte" and "Number". The sequence will increase when "number" changes but will not increase when the previous "number" is the same. Blank or [NULL] values are only possible in the "Time" column and are ignored.

Thank you,
Mike

Book3
	A	B	C	D	E
1	dely_dte	Number	Rte	Time	Sequence
2	03/05/2009	2767713	463	0706	1
3	03/05/2009	2767713	463	0708	1
4	03/05/2009	7047383	463	0835	2
5	03/05/2009	279274	463	0929	3
6	03/05/2009	2763273	463	1026	4
7	03/05/2009	2754677	463	1145	5
8	03/05/2009	273312	463	1437	7
9	03/05/2009	2763790	463	1224	6
10	03/05/2009	2759849	476	0702	1
11	03/05/2009	2771000	476	[NULL]
12	03/05/2009	2761951	476	0906	2
13	03/05/2009	2761951	476	0918	3
14	03/05/2009	2777710	476	1015	4
15	03/05/2009	277403	476	[NULL]
16	03/05/2009	5760058	476	1357	6
17	03/05/2009	5761950	476	1327	5
18	04/10/2009	273106	463	0726	1
19	04/10/2009	2786204	463	0802	2
20	04/10/2009	277368	463	0831	3
21	04/10/2009	2768951	463	1114	6
22	04/10/2009	2774961	463	0924	4
23	04/10/2009	2774961	463	0922	4
24	04/10/2009	7240048	463	1013	5
25	04/10/2009	2779163	463	1456	7
Sheet2

 

[barry houdini]

Hello Mike,

I'm not sure I understand the logic, why are E2 and E3 both 1 but E12 and E13 are 2 and 3?

 

[mikebecker]

Good catch. You're right they should both be the same. Thanks.

Book3
	A	B	C	D	E
1	dely_dte	Number	Rte	Time	Sequence
2	03/05/2009	2767713	463	0706	1
3	03/05/2009	2767713	463	0708	1
4	03/05/2009	7047383	463	0835	2
5	03/05/2009	279274	463	0929	3
6	03/05/2009	2763273	463	1026	4
7	03/05/2009	2754677	463	1145	5
8	03/05/2009	273312	463	1437	7
9	03/05/2009	2763790	463	1224	6
10	03/05/2009	2759849	476	0702	1
11	03/05/2009	2771000	476	[NULL]
12	03/05/2009	2761951	476	0906	2
13	03/05/2009	2761951	476	0918	2
14	03/05/2009	2777710	476	1015	3
15	03/05/2009	277403	476	[NULL]
16	03/05/2009	5760058	476	1357	5
17	03/05/2009	5761950	476	1327	4
18	04/10/2009	273106	463	0726	1
19	04/10/2009	2786204	463	0802	2
20	04/10/2009	277368	463	0831	3
21	04/10/2009	2768951	463	1114	6
22	04/10/2009	2774961	463	0924	4
23	04/10/2009	2774961	463	0922	4
24	04/10/2009	7240048	463	1013	5
25	04/10/2009	2779163	463	1456	7
Sheet2

 

[mikebecker]

I'm still stumped with this one.