Complex reconciliation problem

[Marc L]

Did you have a solution for the problem?

Maybe but not with formulas !​

Maybe from raw data workbook, faster with one to one easy reconcilation rather than 'complex' reconciliation​

and with an elaboration at the level of what any Excel forum expects for …​

​

 

[Hans Noe]

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.

What is your approach for generating this result?

 

[Hans Noe]

Maybe but not with formulas !​

Maybe from raw data workbook, faster with one to one easy reconcilation rather than 'complex' reconciliation​

and with an elaboration at the level of what any Excel forum expects for …​

​

I'm afraid I didn't understand that.
But I think I'm ready to conclude that there isn't an easy solution.

 

[vletm]

Hans Noe
What is your approach for generating this result?
To find pairs or more data, which should belong together.
Do it look useful?
... of course, it could do more. I just test something.
There could be more data to test this.

 

[Marc L]

But I think I'm ready to conclude that there isn't an easy solution.

If a formula solution can't be found under your 365 version then this thread should be back to the VBA forum section​

where some easy logic universal ways could be applied whatever the Excel version like you can find on Excel VBA forums …​

​

 

[Hans Noe]

If a formula solution can't be found under your 365 version then this thread should be back to the VBA forum section​

where some easy logic universal ways could be applied whatever the Excel version like you can find on Excel VBA forums …​

​

Yes, I would like to have it moved back to the VBA section.

 

[Shawnhet]

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.

I think you could probably use VBA to get it to work but I'm not sure I understand the pattern well enough.

For instance, are orders filled sequentially - ie if a pick list is split between two orders is the first order filled first? For instance, if a pick list is for 9 units and there are are 6 left to fill in order 1 - do all 6 go to that order and the remaining 3 to the next one in sequence or are there other patterns possible e.g. 5-4, 1-8,7-2,2-7)?

if it is totally random, I'm not sure I will be able to figure it out.

 

[Shawnhet]

I was giving your problem some thought and I thought I would try and summarize what information we can pull from your tables - see attached.

Let's see if I understand the problem correctly - I will give you several cases and you can let me know if I have missed anything.

By taking the sums by order number for NAV and DdD we can calculate the variance between the two.

Case 1 - If there is no variance, all is good - no further work needed

Case 2 (blue)- If there is a variance and all the PLs are tied exclusively to that order, then that means that part of the order was filled in the previous month or part of the order is still unfilled. If filled previously, check previous month's outstanding to clear.

If remaining to clear, record somewhere for next month's reconciliation.

Case 3 - (yellow) - If there is a variance and there are PLs in the order that also apply to another order, those will need to manually checked to see which orders have been completely filled and which are still outstanding. Orders with items outstanding will have to be recorded for next month's reconciliation.

Case 4 - items which are on the DdD list but not the NAV list - presumably these were deliveries from prior months NAV reports - recording of discrepancies from 2 and 3 should stop this being an issue.

Case 5 - items which are on the NAV list but not the DdD list - these should be recorded and used in next month's reconciliation.

Is this reasonable understanding of the situation?

Thanks,

 

[Hans Noe]

I was giving your problem some thought and I thought I would try and summarize what information we can pull from your tables - see attached.

Let's see if I understand the problem correctly - I will give you several cases and you can let me know if I have missed anything.

By taking the sums by order number for NAV and DdD we can calculate the variance between the two.

Case 1 - If there is no variance, all is good - no further work needed

Case 2 (blue)- If there is a variance and all the PLs are tied exclusively to that order, then that means that part of the order was filled in the previous month or part of the order is still unfilled. If filled previously, check previous month's outstanding to clear.

If remaining to clear, record somewhere for next month's reconciliation.

Case 3 - (yellow) - If there is a variance and there are PLs in the order that also apply to another order, those will need to manually checked to see which orders have been completely filled and which are still outstanding. Orders with items outstanding will have to be recorded for next month's reconciliation.

Case 4 - items which are on the DdD list but not the NAV list - presumably these were deliveries from prior months NAV reports - recording of discrepancies from 2 and 3 should stop this being an issue.

Case 5 - items which are on the NAV list but not the DdD list - these should be recorded and used in next month's reconciliation.

Is this reasonable understanding of the situation?

Thanks,

That is an interesting approach, I wish you'd left the formulas in so I can see what you're doing.
How do you arrive at the result in column F?
I agree that the white and the blue lines can be cleared using your method, if it's entirely formula driven.

 

[Shawnhet]

Yes, I am just using formulae so far.

Column F is just this formula - on your original table 3 - it returns false if a picklist is used for more than 1 order.

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

Columns C and D are just SUMIFS by the order number for tables 1 and 2 (duplicates removed for clarity) and column G is a COUNTIFS of PLS by order#.

Let me know if you need any further detail.

Thanks,

 

[Shawnhet]

Hi,

On looking at what I had previously given you I thought I might have skipped a step or two in my explanation so here is my full spreadsheet with formulas.

I believe you should be able to just paste new months data on top of old and keep the formulae in place.

I have added a few columns to your original sheet - by filtering for red text in columns E and I you can find records that are on NAV but not DdD and vice versa in column I respectively.

I believe the missing items in column I should resolve themselves once all the outstanding items from previous months are included in your original tables.

Missing items in column E, will need to be held and reconciled next month.

Then the sample output page. is presented with formulae, where it can be filtered to create the white, yellow and blue.

Without understanding more about the logic of how the orders are assigned to picklists, I don't think I can help with the yellows. However, they have been reduced to a small fraction of what you started with I think.

However, it does occur to me that it is possible that some of the blue lines may be resolved in the current month once you are able to go through the yellow lines - it would probably make sense to do them first before migrating them to the file for next months reconciliation.