Sequence Values In order but need to be Unique

[Riddlemethis]

Data dump fills out cells. In Column P the data will give the start time of certain shifts.
Column AG will have a sequence of numbers 1 to whatever the total number of start times there is that day.
Column AF fills out after macro is run to copy and paste the start times.
Column Q returns the Sequence number via vlookup.

So problem being i would need each start time to have it's own unique number even though there may be more that one say 09:00:00 start time... As you can see
Is their a method to have the sequence or vlookup return a unique value if there is a duplicate value dropped into AF? It doesn't matter what it returns as long as the value is higher than a start time earlier an lower than a start time later.

 

[Peter_SSs]

Is this what you mean?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

22 11 27.xlsm
	P	Q															AF	AG
1																	07:30:00	1
2	10:00:00	4															09:00:00	2
3																	09:45:00	3
4	10:15:00	8															10:00:00	4
5																	10:00:00	5
6	10:45:00																10:00:00	6
7																	10:00:00	7
8	09:45:00	3															10:15:00	8
9																	10:15:00	9
10	10:00:00	5
11
12	10:15:00	9
13
14	10:00:00	6
Sequence

Cell Formulas
Range	Formula
Q2:Q14	Q2	=IFERROR(AGGREGATE(15,6,$AG$1:$AG$150/($AF$1:$AF$150=P2),COUNTIF($P$2:$P2,$P2)),"")

 

[Riddlemethis]

My Bad, I knew XL2BB existed and was lazy on this one..

Your solution is almost perfect and appreciated. is it possible though for the first value in your formula to be '1' and not blank? as in cell Q55

DRB Test.xlsm
	P	Q															AF	AG
1		Sequence															09:00:00	1
2	10:00:00	4															09:00:00	2
3		0															09:30:00	3
4		0															10:00:00	4
5		0															10:30:00	5
6		0															10:30:00	6
7		0															11:00:00	7
8	09:30:00	3															11:30:00	8
9		0															11:30:00	9
10		0															13:00:00	10
11		0															14:00:00	11
12		0															END
13		0
14	10:30:00	5
15		0
16		0
17		0
18		0
19		0
20	09:00:00	2
21		0
22		0
23		0
24		0
25		0
26	11:30:00	8
27		0
28		0
29		0
30		0
31		0
32	14:00:00	11
33		0
34		0
35		0
36		0
37	11:30:00	9
38		0
39		0
40		0
41		0
42		0
43	11:00:00	7
44		0
45		0
46		0
47		0
48		0
49	10:30:00	6
50		0
51		0
52		0
53		0
54		0
55	09:00:00
56		0
57		0
58		0
59		0
60		0
61	13:00:00	10
62		0
63		0
64		0
65		0
66	END	0
67		0
68		0
69		0
Data

Cell Formulas
Range	Formula
AG1:AG11	AG1	=SEQUENCE(K151,1,1,1)
P2:P69	P2	=IFS(K2=1,(RIGHT(D3,8)),L2=1,(RIGHT(D3,8)),M2=1,(RIGHT(D3,8)),N2=1,"END",TRUE,"")
Q2:Q69	Q2	=IFERROR(AGGREGATE(15,6,$AG$2:$AG$150/($AF$2:$AF$150=P2),COUNTIF($P$2:$P2,$P2)),"")
Dynamic array formulas.

 

[Peter_SSs]

There are, or could be, a few issues.

1. Your formulas in column P refer to a different row on the left of the sheet. For example, the P2 formula refers to K2:N2 but then D3. Is that correct?
2. The formulas in column P are returning a text time but the times in column AF appear to be actual times (that is, numbers)
3. I had made a mistake with my column Q formula (corrected since but looks like you had seen & used the original). The table in columns AF:AG starts in row 1, not row 2

So, try this revised formula in P2. It should address points 2 and 3 above.

=IFERROR(AGGREGATE(15,6,$AG$1:$AG$150/($AF$1:$AF$150=P2+0),COUNTIF($P$2:$P2,$P2)),"")

 

[Riddlemethis]

getting all "" ( Blanks ) now. Tried formatting all 3 columns as text time and general and getting "" as the result either way. In regards to your points 1 & 2 you correct in both. Opening this up as much as i can as i appreciate your efferts altough now I think the outcome will be towards my inventable daftness in formatting data correctly??

						Sequence	TU	Run	Drop 1	Drop 2	Drop 3	Drop 4	Drop 5	Drop 6	Drop 7	Drop 8	Start Trigger	Gap Count	Shift Type		09:00:00	1
1					10:00:00												1	5	DoubleShift		09:00:00	2
																					09:30:00	3
							45686	NI660	NI660												10:00:00	4
							45707	NI415,NI412,NI989,NI416	NI415	NI412	NI989	NI416									10:30:00	5
							45697	NI989,NI412	NI989	NI412											10:30:00	6
							45681	NI407,NI563	NI407	NI563											11:00:00	7
1					09:30:00												1	5	DoubleShift		11:30:00	8
																					11:30:00	9
							45710	NI980,NI422	NI980	NI422											13:00:00	10
							45678	NI996,NI420	NI996	NI420											14:00:00	11
							45665	NI401,NI661	NI401	NI661											END
							45692	NI980,NI982	NI980	NI982
1					10:30:00												1	5	DoubleShift
							45700	NI411,NI993	NI411	NI993
							45689	NI402,NI678	NI402	NI678
							45664	NI989,NI400	NI989	NI400
							45711	NI403,NI422	NI403	NI422
	1				09:00:00												1	5	DoubleShift
							45704	NI406,NI414,NI407	NI406	NI414	NI407
							45679	NI566,NI424	NI566	NI424
							45682	NI564,NI998	NI564	NI998
							45690	NI980	NI980
	1				11:30:00												1	5	DoubleShift
							45708	NI413,NI401,NI424,NI566	NI413	NI401	NI424	NI566
							45673	NI414,NI564	NI414	NI564
							45680	NI411,NI528	NI411	NI528
							45667	NI403,NI418	NI403	NI418
	1				14:00:00												1	4	3x3
							45668	NI406	NI406
							45691	NI981	NI981
							45685	NI650	NI650
	1				11:30:00												1	5	DoubleShift
							45666	NI402,NI418	NI402	NI418
							45688	NI998,NI413	NI998	NI413
							45676	NI982,NI417	NI982	NI417
							45669	NI407	NI407
	1				11:00:00												1	5	DoubleShift
							45702	NI997	NI997
							45709	NI420,NI998,NI400,NI661	NI420	NI998	NI400	NI661
							45670	NI981,NI408	NI981	NI408
							45699	NI982,NI991	NI982	NI991
	1				10:30:00												1	5	DoubleShift
							45672	NI416,NI412	NI416	NI412
							45705	NI411,NI996,NI986,NI564	NI411	NI996	NI986	NI564
							45698	NI990,NI987	NI990	NI987
							45696	NI988	NI988
		1			09:00:00												1	5	DoubleShift
							45674	NI567,NI996	NI567	NI996
							45706	NI660,NI650,NI983,NI678	NI660	NI650	NI983	NI678
							45684	NI567,NI997	NI567	NI997
							45693	NI983	NI983
		1			13:00:00												1	4	3x3
							45694	NI986	NI986
							45687	NI661	NI661
							45695	NI987	NI987
			1		END												1	?	END

 

[Peter_SSs]

Tried formatting all 3 columns as ..

Changing the format of a column does not change the underlying values in the cells. If they are text then they stay as text etc. Formatting only (possibly) changes the appearance.

Can you post that XL2BB again using Mini Sheet not Table Only after you have first hidden the irrelevant columns? Can you ensure we see columns D, P, Q (with my formula inserted in that column), AF & AG. About 30 rows should be plenty.

 

[Riddlemethis]

DRB Test.xlsm
	D												P	Q															AF	AG
1	Start													Sequence															09:00:00	1
2													10:00:00																09:00:00	2
3	27.11 10:00:00																												09:30:00	3
4	27.11 10:00:00																												10:00:00	4
5	27.11 14:23:36																												10:30:00	5
6	27.11 20:46:54																												10:30:00	6
7	28.11 01:43:30																												11:00:00	7
8													09:30:00																11:30:00	8
9	27.11 09:30:00																												11:30:00	9
10	27.11 09:30:00																												13:00:00	10
11	27.11 17:01:30																												14:00:00	11
12	27.11 21:03:54																												END
13	28.11 00:56:48
14													10:30:00
15	27.11 10:30:00
16	27.11 10:30:00
17	27.11 15:31:48
18	27.11 20:33:42
19	28.11 01:11:18
20													09:00:00
21	27.11 09:00:00
22	27.11 09:00:00
23	27.11 16:19:30
24	27.11 20:00:42
25	28.11 00:22:42
26													11:30:00
27	27.11 11:30:00
28	27.11 11:30:00
29	27.11 15:59:30
30	27.11 22:48:00
31	28.11 03:35:36
32													14:00:00
33	27.11 14:00:00
34	27.11 14:00:00
35	27.11 17:50:58
36	27.11 22:26:04
37													11:30:00
38	27.11 11:30:00
39	27.11 11:30:00
40	27.11 16:27:06
41	27.11 21:31:30
42	28.11 02:07:54
43													11:00:00
44	27.11 11:00:00
45	27.11 11:00:00
46	27.11 15:14:12
47	27.11 20:28:12
48	28.11 00:57:36
49													10:30:00
Data

Cell Formulas
Range	Formula
AG1:AG11	AG1	=SEQUENCE(K151,1,1,1)
P2:P49	P2	=IFS(K2=1,(RIGHT(D3,8)),L2=1,(RIGHT(D3,8)),M2=1,(RIGHT(D3,8)),N2=1,"END",TRUE,"")
Q2:Q49	Q2	=IFERROR(AGGREGATE(15,6,$AG$1:$AG$150/($AF$1:$AF$150=P2+0),COUNTIF($P$2:$P2,$P2)),"")
Dynamic array formulas.

 

[Riddlemethis]

@Peter_SSs re ran your original formula after your correction and it works perfectly. Thank you for your help!!

 

[Peter_SSs]

You're welcome. Glad you got it working. Thanks for the follow-up.