Complex reconciliation problem

Hans Noe

New Member
Joined
Jun 21, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Greetings fellow Excel enthusiasts.

For several months now I've failed to solve this task:

My client has an ERP system (NAV) for HQ level operations and a retail system (DdD) for store level operations.
Goods are regularly sent from HQ to stores.
I need to come up with a way to check that goods sent in one system = goods sent in the other system.
There are a few curve balls:
a. The retail system automatically records goods received according to the pick list number on the invoice
b. The HQ system records goods sent according to the invoice number, I can translate this into delivery numbers for easier comparison
c. I can't match pick list numbers to delivery numbers directly, only indirectly through order numbers which are registered for both pick list numbers and delivery numbers
d. Each pick list can have multiple order numbers and each order number can have multiple pick list numbers
e. Dates don't match between the systems, they're often registered in the retail system a day or two after the invoice date (automatically)

Due to the date issue there'll always be a discrepancy between what's recorded in each system.
I need to show each month that the discrepancy is solely due to some goods being registered in different months.

Data for a single store is shown below, there are 20 in total (shown as customer numbers).
I've checked this by hand and highlighted all pairs.
Green = simple 1to1 match, one pick list number matches a single delivery number
Blue = simple 2to2 match, two pick list numbers match two delivery numbers through a single order number
Red = mismatch
Orange = complex match (120 units total): In this case I first combined all S039058 lines, then tacked on S040633 and S040426 because PL017178 has a link to each of those, and finally S040426 has a link to PL017148

I can automate locating green and blue matches, but that still leaves more manual work than I care for.
I also have to transfer this task to a colleague eventually, and that colleague may not be able to learn how to match manually as with the orange lines.

If anyone is way smarter than me and able to come up with an automated solution, I'd be most grateful.
I imagine complex VBA can do the job iteratively, but I'm hoping for something more elegant.
Please ask if my explanation wasn't coherent.

Cheers,
Hans from Denmark

Upload.PNG
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,693
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Complex reconciliation problem
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

Forum statistics

Threads
1,181,426
Messages
5,929,822
Members
436,697
Latest member
sunnypl

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
Top