Formula to identify gaps

[uiucfan1]

All - I'm looking for a formula that can identify gaps in a LARGE set of data. I'm going to copy and paste an example into this question. So, from here - i can see that there is no tag #14 or #25, but i have LITERALLY almost 6K rows of data to look through - so I'm hoping someone smarter than me can help me with a formula that I can quickly review to determine either:

1) a listing of all the gaps; or
2) conditional format when there are gaps identified

I can't just use a "+1" function, because when there are gaps - i want to still be looking "one forward"... so, like above - when it skips #14 and goes from 13 to 15 - is there an automatic way to do this - or do I have to just skip gap to gap and redo any applicable formula?

Thanks in advance!!!!!

UST Population.xlsx
	C	D		F
40	U000001	12/12/2018
41	U000002	12/14/2018
42	U000003	1/2/2019
43	U000004	1/2/2019
44	U000005	1/9/2019
45	U000006	1/10/2019
46	U000007	1/28/2019
47	U000008	1/28/2019
48	U000009	2/5/2019
49	U000010	2/7/2019
50	U000011	2/28/2019
51	U000012	3/6/2019
52	U000013	3/26/2019
53	U000015	3/28/2019
54	U000016	4/3/2019
55	U000017	4/3/2019
56	U000018	4/10/2019
57	U000019	4/17/2019
58	U000020	4/17/2019
59	U000021	4/19/2019
60	U000022	4/22/2019
61	U000023	4/30/2019
62	U000024	5/2/2019
63	U000026	5/15/2019
64	U000027	5/16/2019
65	U000028	6/3/2019
66	U000029	6/17/2019
67	U000030	6/20/2019
Sorted by Tag #

 

[Anonymous1378]

Try this?

Book6
	C	D	E
40	U000001		U000014
41	U000002		U000025
42	U000003
43	U000004
44	U000005
45	U000006
46	U000007
47	U000008
48	U000009
49	U000010
50	U000011
51	U000012
52	U000013
53	U000015
54	U000016
55	U000017
56	U000018
57	U000019
58	U000020
59	U000021
60	U000022
61	U000023
62	U000024
63	U000026
64	U000027
65	U000028
66	U000029
67	U000030
Sheet1

Cell Formulas
Range	Formula
E40:E41	E40	=LET(_a,TEXT(SEQUENCE(MAX(--MID(C40:C67,2,99))),"U000000"),FILTER(_a,ISNA(XMATCH(_a,C40:C67))))
Dynamic array formulas.

 

[uiucfan1]

Try this?

Book6
	C	D	E
40	U000001		U000014
41	U000002		U000025
42	U000003
43	U000004
44	U000005
45	U000006
46	U000007
47	U000008
48	U000009
49	U000010
50	U000011
51	U000012
52	U000013
53	U000015
54	U000016
55	U000017
56	U000018
57	U000019
58	U000020
59	U000021
60	U000022
61	U000023
62	U000024
63	U000026
64	U000027
65	U000028
66	U000029
67	U000030
Sheet1

Cell Formulas
Range	Formula
E40:E41	E40	=LET(_a,TEXT(SEQUENCE(MAX(--MID(C40:C67,2,99))),"U000000"),FILTER(_a,ISNA(XMATCH(_a,C40:C67))))
Dynamic array formulas.

I tried that - and it worked great for the information in that cell range (after adjusting for the end of the formula) ... then i tried moving it a few cells over for "year 2" and couldn't get it to work correctly - so i moved my "year 2" data into a new sheet over-righting "year 1" ... and pasted again - i changed the range and the "u" to a "v" as the tag #s changed.... but something is off - its skipping from 200 to 1100 even though 201, 202, etc. are present - any ideas? I have included the mini sheet to show what i used.

UST Population.xlsx
	C	D		F
41	V000101	6/10/2020		V000120
42	V000102	6/11/2020		V000121
43	V000103	6/11/2020		V000122
44	V000104	6/17/2020		V000123
45	V000105	6/22/2020		V000124
46	V000106	6/22/2020		V000125
47	V000107	6/22/2020		V000126
48	V000108	6/24/2020		V000127
49	V000109	6/23/2020		V000128
50	V000110	7/15/2020		V000129
51	V000111	6/23/2020		V000130
52	V000112	6/24/2020		V000131
53	V000113	6/25/2020		V000132
54	V000114	7/6/2020		V000133
55	V000115	7/9/2020		V000134
56	V000116	7/15/2020		V000135
57	V000117	7/16/2020		V000136
58	V000118	7/17/2020		V000137
59	V000119	7/17/2020		V000138
60	V000146	1/5/2021		V000139
61	V000178	9/28/2020		V000140
62	V000179	10/30/2020		V000141
63	V000180	10/30/2020		V000142
64	V000181	11/4/2020		V000143
65	V000182	11/4/2020		V000144
66	V000183	11/13/2020		V000145
67	V000184	11/20/2020		V000147
68	V000185	11/18/2020		V000148
69	V000186	11/23/2020		V000149
70	V000187	12/8/2020		V000150
71	V000201	1/21/2020		V000151
72	V000202	1/29/2020		V000152
73	V000203	2/20/2020		V000153
74	V000204	3/12/2020		V000154
75	V000205	3/16/2020		V000155
76	V000206	5/21/2020		V000156
77	V000207	5/22/2020		V000157
78	V000208	5/18/2020		V000158
79	V000209	5/21/2020		V000159
80	V000210	6/2/2020		V000160
81	V000211	6/2/2020		V000161
82	V000212	6/2/2020		V000162
83	V000213	6/3/2020		V000163
84	V000214	6/4/2020		V000164
85	V000215	6/10/2020		V000165
86	V000216	8/11/2020		V000166
87	V000217	6/11/2020		V000167
88	V000218	6/12/2020		V000168
89	V000219	6/12/2020		V000169
90	V000220	6/15/2020		V000170
91	V000221	6/15/2020		V000171
92	V000222	6/23/2020		V000172
93	V000223	7/7/2020		V000173
94	V000225	7/1/2020		V000174
95	V000227	7/7/2020		V000175
96	V000228	7/7/2020		V000176
97	V000229	7/14/2020		V000177
98	V000230	7/29/2020		V000188
99	V000231	7/30/2020		V000189
100	V000233	8/4/2020		V000190
101	V000235	8/4/2020		V000191
102	V000236	8/10/2020		V000192
103	V000237	8/11/2020		V000193
104	V000238	8/11/2020		V000194
105	V000239	8/11/2020		V000195
106	V000241	8/13/2020		V000196
107	V000242	8/13/2020		V000197
108	V000243	8/24/2020		V000198
109	V000244	8/25/2020		V000199
110	V000245	8/25/2020		V001100
2022 - Sorted by Tag #

Cell Formulas
Range	Formula
F41:F5450	F41	=LET(_a,TEXT(SEQUENCE(MAX(--MID(C41:C2750,2,99))),"V000100"),FILTER(_a,ISNA(XMATCH(_a,C41:C2750))))
Dynamic array formulas.

 

[Fluff]

How about

=LET(d,C41:C3000,a,--REPLACE(FILTER(d,d<>""),1,1,""),t,TEXT(SEQUENCE(MAX(a)-MIN(a)+1,,MIN(a)),"V000000"),FILTER(t,ISNA(XMATCH(t,d))))

 

[uiucfan1]

How about

=LET(d,C41:C3000,a,--REPLACE(FILTER(d,d<>""),1,1,""),t,TEXT(SEQUENCE(MAX(a)-MIN(a)+1,,MIN(a)),"V000000"),FILTER(t,ISNA(XMATCH(t,d))))

This appears to have done it Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback.