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
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
