A macro to identify data items not present in one of two sheets

Hi all,

This is my first post here.

There is a problem that I have been trying to solve for quite some time now, but haven’t quite hit it yet.

Problem description:
I am working with the financial accounting for multiple companies. There is a task I perform where I have to identify invoices which are not booked in one company or another.

There are two sheets of data, one is the payable side of Company 1, other is the receivable side of Company 2. A sample file is attached.

My aim is to create a macro that helps to determine which invoices (in this case 200023 abc6 2000) are not booked in the payable side of Company 1.
It would be perfect if a new sheet would be created where a list of invoices not found in Company 1 is given.


Could you please help me? Appreciate your help and attention.


example.xlsx

Thanks a lot Tinbendr!

The new sheet would ideally contain all columns for the missing invoices.

Your macro returns these results:
Invoice
2845893118
3237675073
200023

How come?

Also, say I have 6 companies that have to be reconciled both ways Comp 1 AR with Comp 2 AP and Comp 2 AR with Comp1 AP.
It would be perfect to have all missing invoices for all companies shown in that one new sheet. Is that even possible?

Thanks and best regards,

Gustavs

Sorry for the delayed answer.

Yes, well, actually there are 6 sheets with pivots in them. The pivots are using data which is imported from the accounting software database from time to time.
By clicking on the pivots I get the payables data from 1 company with and the receivables data from the other company.

These are the pairs that have any active transactions between them (and thus data to have to check)

01 with 02
with 03
with 04
with 05
with 40
02 with 03
with 04
with 05
03 with 04
with 05
04 with 05

I am sure it would be a lot faster to explain if I just attached a sample file, but I am not sure I am allowed to do that.

So the macro would:
Select the correct data arrays from the pivot (even with the pivot being changed time to time)
check which invoices are not booked in which side.

To Attach a File:

1. Click on Go Advanced (below the Quick Reply area)
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.