Complex reconciliation problem

[Hans Noe]

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 in the spreadsheet, 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

 

[Marc L]

I imagine complex VBA can do the job iteratively, but I'm hoping for something more elegant.

Hi, should be with smart data but when it's not the case like here no other way …​

​

 

[Hans Noe]

Hi, should be with smart data but when it's not the case no other way …​

​

So is this unsolvable without VBA?

 

[Marc L]

I don't know as we are in the VBA forum section …​

​

 

[Marc L]

And according to your attachment it looks like more an one to one reconciliation rather than a 'complex reconciliation problem' …​

​

 

[Hans Noe]

And according to your attachment it looks like more an one to one reconciliation rather than a 'complex reconciliation problem' …​

​

I posted it in the general section, someone moved it here for some reason.
It's complex because it's a many-to-many reconciliation.
Are you able to solve this with formulas alone?

 

[Marc L]

No if it is really 'complex' …​

For an easy one to one as it looks like so maybe it could be achieved with conditional formatting,​

do you want this thread moved back to the Excel forum section ?​

​

 

[Hans Noe]

No if it is really 'complex' …​

​

For an easy one to one as it looks like so maybe it could be achieved with conditional formatting,​

do you want this thread moved back to the Excel forum section ?​

​

Yes please.
I still contend that it's not easy, but I'm looking forward to seeing the elegant solution that I've been missing.

 

[Marc L]

Thread moved to Ask an Excel question forum section …​

​

Formulas helpers must know which Excel version you use ?​

​

 

[Hans Noe]

Version is 365.

 

[Shawnhet]

Wow, this is a very convoluted system you have to work with.

However, I was thinking that you could get an order number for table 2 by using an Index - Match on table three. You could then compare the SUMIFS (using that derived order number) to compare the totals from Tables 2 and 3. The following formula should work for that:

=INDEX(J:J,MATCH(1,(F3=L:L)*(G3=K:K),0)) - this is an array formula hit CTRL+Shift+Enter - if done right the formula will be wrapped in {}

This should associate an order number with

The only remaining issue should be for orders with PLs that are shared with more than 1 order.
This could be demonstrated by something like the following on table 3 - filtered for FALSE.

=COUNTIFS(L:L,L3,K:K,K3)=1

I can't see any other option but to manually work with these false ones to see if you can manually account for the discrepancies.

Anyway, this is probably my best effort for now.

Good luck,

 

[bosco_yip]

...............................
=INDEX(J:J,MATCH(1,(F3=L:L)*(G3=K:K),0)) - this is an array formula hit CTRL+Shift+Enter -
..............................

I haven't study the OP's question clearly yet.

But,

This 2 conditional Lookup formula has a risk.

=INDEX(J:J,MATCH(1,(F3=L:L)*(G3=K:K),0))

Don't used whole column range for conditional multiplication. It is one million cells per column in a xlsx file.

So,

1 million cells X 1 million cells calculation will slow down your computer speed, and caused computer breakdown in some old Excel versions.

This is advice to use limited cells range to reduce the risk, something like this :

=INDEX(J1:J1000,MATCH(1,(F3=L1:L1000)*(G3=K1:K1000),0))

Regards

 

[vletm]

Hans Noe
Could this output be one step forward?
... of course, this needs few more steps.

 

[Hans Noe]

Hans Noe
Could this output be one step forward?
... of course, this needs few more steps.

I'm afraid I'm unable to follow what you did, can you elaborate?
Also, are you aware that the lines need to be matched separately per customer number?

 

[vletm]

Hans Noe
That layout should show how those three 'part' has connection with others.
eg row 13 - all three 'part's can match as those colors


... but Amounts ... not!
NAV data has 6+2 and DdD data 7

 

[Hans Noe]

How did you create this output?

Also, are you aware that row 13+14+16 match up but row 15 does not?

 

[Hans Noe]

Hi, should be with smart data but when it's not the case like here no other way …​

​

Did you have a solution for the problem?

 

[Hans Noe]

Wow, this is a very convoluted system you have to work with.

However, I was thinking that you could get an order number for table 2 by using an Index - Match on table three. You could then compare the SUMIFS (using that derived order number) to compare the totals from Tables 2 and 3. The following formula should work for that:

=INDEX(J:J,MATCH(1,(F3=L:L)*(G3=K:K),0)) - this is an array formula hit CTRL+Shift+Enter - if done right the formula will be wrapped in {}

This should associate an order number with

The only remaining issue should be for orders with PLs that are shared with more than 1 order.
This could be demonstrated by something like the following on table 3 - filtered for FALSE.

=COUNTIFS(L:L,L3,K:K,K3)=1

I can't see any other option but to manually work with these false ones to see if you can manually account for the discrepancies.

Anyway, this is probably my best effort for now.

Good luck,

Thank you for appreciating the nonsenseness of the system
I was able to get that far myself as well.
Handling the rest of it manually would be manageable if I stayed in charge of the task, but I'll be handing it over in a couple months and I don't think my successor will be able to do that.

I guess if I can get confirmation that this can't be solved without VBA then I can at least get some peace of mind, I feel like such a newbie that I haven't solved it already.

 

[vletm]

I tested to do it 'my way'.

For my eyes
> rows 13 & 14 belongs to one case ... but with amount difference; one (1).
> row 15 is other case - and there seems to be everything okay.

So far, I tested to create layout - for me it looks readable.
It is not ready even for me - if I should use it.
eg to hide 'clear cases'

For my eyes, there are not any ... the problem ... what ever do You mean?
There are some datas, which do not match, for some reason.

 

[Hans Noe]

I tested to do it 'my way'.

For my eyes
> rows 13 & 14 belongs to one case ... but with amount difference; one (1).
> row 15 is other case - and there seems to be everything okay.

So far, I tested to create layout - for me it looks readable.
It is not ready even for me - if I should use it.
eg to hide 'clear cases'

For my eyes, there are not any ... the problem ... what ever do You mean?
There are some datas, which do not match, for some reason.

But row 16 is joined with 13 + 14 as well due to same PL number and customer number.

 

[vletm]

for me:
16 is different order than 13&14

 

[Hans Noe]

for me:
16 is different order than 13&14
View attachment 79595

Then I've expressed myself poorly. They're a match if they share order number or PL number.
In this case they share PL number PL016981.

 

[vletm]

Hans Noe
Is there more combinations (matches), which are as above?

 

[Hans Noe]

Hans Noe
Is there more combinations (matches), which are as above?

Another example for customer 20014:
Rows 316 + 317 + 391 are joined with rows 101 through 107.
The links are in rows 117 + 135 + 136 + 201 + 301 + 380 which you do not use.

 

[vletm]

Hans Noe
I see ...
Even minor missing details could mean ... start from zero.
Here version2 -layout.
There are some texts for You
and
there should still do some manual checking.