All sequences not found in batch to be placed in separate worksheet

[shili12]

A follow up from this thread, All sequences required from this range
let me define my problem objectively :-
I have 2 worksheets, fleet and non fleet, all with certificates in unique sequence,

non fleet	fleet
PeriodCertificate	Certificate
C24185668	B11816877
C24185651	B11816631
C24185667	B11822719
C24724059	B11822720
C24724058	B11821813
C24724065	B11817175
C24724187	B11821820
C24185660	B11816874
C24724060	B11826164
C24724167	B11816878
C24724061	B11826610
C24724171	0
C24724177	B11844710
	B11829889
etc etc 230000 lines	0

the table/range I have from issuer of certificates is in a batch mode. see below
The goal: Find out which certificates in batch mode are not issued in unique sequence ( above) and place in it separate worksheet.

As i use excel 365, any method is acceptable , VBA, formulae, LET, lambda,

#	Transaction Date	Company Name	Certificate Type	First Serial #	Last Serial #	Qty(Out/In)	User Name	Available Balance
22564​	21/06/2022 12:12​	Acme Insurance Company Ltd.	Type B - Commercial Vehicle	B12316070	B12316099	30​	Hemstone	34​
22594​	21/06/2022 14:22​	Acme Insurance Company Ltd.	Type B - Commercial Vehicle	B12337528	B12338527	1000​	Kimanzi	1005​
22652​	21/06/2022 16:28​	Acme Insurance Company Ltd.	Type B - Commercial Vehicle	B12336312	B12336321	10​	Mary	600​
51092​	12/05/2022 11:56​	Acme Insurance Company Ltd.	Type C - Private Car	C22568962	C23249190	25​	Faith	3751​
51097​	12/05/2022 11:57​	Acme Insurance Company Ltd.	Type C - Private Car	C23500530	C23500539	10​	Faith	3777​
51098​	12/05/2022 11:58​	Acme Insurance Company Ltd.	Type C - Private Car	C25380547	C25380556	10​	Faith	3787​
3442​	09/06/2022 11:19​	Acme Insurance Company Ltd.	Type D - PSV	D4586425	D4586446	-22​	Blanche	369​
3443​	09/06/2022 11:26​	Acme Insurance Company Ltd.	Type D - PSV	D4586468	D4586471	-25​	Blanche	344​
3652​	24/06/2022 14:23​	Acme Insurance Company Ltd.	Type D - PSV	D4551255	D4551258	-4​	Blanche	450​

 

[Marc L]

According to your attachment a starter VBA demonstration to paste only to the Result worksheet module :​

Sub Demo2()
        UsedRange.Offset(1).Clear
        Application.ScreenUpdating = False
    With CreateObject("Scripting.Dictionary")
        For Each V In [nonfleetcompany!A1].CurrentRegion.Columns(27).Value2:  .Item(V) = Empty:  Next
        For Each V In [fleetcompany!A1].CurrentRegion.Columns(27).Value2:     .Item(V) = Empty:  Next
        W = [Fromissuer!A1].CurrentRegion.Columns("E:F")
    For R& = 2 To UBound(W)
            P& = Mid(W(R, 1), 2) - 1
            N& = Mid(W(R, 2), 2) - P
            ReDim T$(1 To N, 0)
        For Each V In Evaluate("""" & Left(W(R, 1), 1) & """&ROW(1:" & N & ")+" & P)
            If Not .Exists(V) Then L& = L& + 1: T(L, 0) = V
        Next
            If L Then Cells(Rows.Count, 1).End(xlUp)(2).Resize(L) = T: L = 0
    Next
       .RemoveAll
    End With
        Application.ScreenUpdating = True
End Sub

 

[shili12]

Thank you. If it works well given that it can fit within the excel column which it's most likely to do so, it will help me in detection of fraudulent activities.

 

[Marc L]

Post #2 demonstration edited for optimization …​

​