vba missing invoice checking

[Es09094]

Hi all,
I am having a repetitive task and i can not even call myself a vba user.

Description
Every day as well as once in a moth i should find missing invoices of multiple co partners.
Every one of those co-partners(co parter number) have a unique number lets say z1, z2.... Zn .
The invoices received (reference ) of each of them have a unique format and should be in a sequence,
for example z1 sequence of invoices corresponds to AAA12345, AAA12346, AAA12347
z2 sequence corresponds AABAsd123451 AABAsd123452 AABAsd123453
.....

Zn sequence corresponds to Znxyx, whatever of the same textnumber.
Every day they inform me if an invoice gets canceled .
Every invoice have multiple products (Product)and the extract of my system is per product .

My Thoughts
I should split the extract from my system into every co partner worksheet,
Update every copartner worksheet with the cancelled invoices finding the last empty row in their worksheet
I should loop through in every co partner worksheet and remove duplicates of invoices ( if an invoice have more than one products turn to be duplicate)
I should extract the text and number of every unique invoice per co partner with a function and loop through the same formula till the total amount of his / her invoices.
Then i should sort them up in ascending or descending order . The missing invoices should be those that have a numeric difference greater than 1 from the previous ones.
Create a summary sheet that will present the results per copartner.
Any ideas?

 

[Akuini]

@Es09094
Welcome to MrExcel.
Do the invoices always have some letters followed by some numbers?
But the example in sheet Input shows some invoices (reference in col L) that only have numbers in it.

I should split the extract from my system into every co partner worksheet,

You don't need that, sheet Input would be enough.

Could you post your sample data in sheet Input as a table not image? so I can use that to test the macro I plan to write (to get the missing invoice number).
OR
Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
Note: the example should have some missing invoices.