I'm afraid I didn't understand that.Maybe but not with formulas !Maybe from raw data workbook, faster with one to one easy reconcilation rather than 'complex' reconciliationand with an elaboration at the level of what any Excel forum expects for …
But I think I'm ready to conclude that there isn't an easy solution.
Yes, I would like to have it moved back to the VBA section.If a formula solution can't be found under your 365 version then this thread should be back to the VBA forum sectionwhere some easy logic universal ways could be applied whatever the Excel version like you can find on Excel VBA forums …
I think you could probably use VBA to get it to work but I'm not sure I understand the pattern well enough.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.
That is an interesting approach, I wish you'd left the formulas in so I can see what you're doing.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?