vba missing invoice checking

Es09094

New Member
Joined
Mar 1, 2022
Messages
1
Office Version
  1. 365
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?
 

Attachments

  • inputs.PNG
    inputs.PNG
    70.1 KB · Views: 16
  • summary.PNG
    summary.PNG
    32.9 KB · Views: 16

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
@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.
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top