shili12
Member
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,
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,
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 |
Attachments
Last edited: