Reconciliation Logic without using Multi-Row formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I have to define the workflow for reconciliation please help me in this.
Condition: -
1. We cant use Multi-row formula.
2. First it will check for same check number in the same column (Check Number1), if it is found then it will search for same amount present in same column(Amount1) with opposite sign then it is reconciled and deleted from the data.
For e.g.
102 | A102 | -200 | ||
102 | A102 | 200 |
the above data is reconciled.
3. Then, it will check for same check number in the same column, if it is found then it will search for same amount present in another column(amount2) with same sign then it is reconciled and deleted from the data.
For e.g.
102 | A102 | -200 | ||
102 | A102 | -200 |
the above data is reconciled
Input Data: -
Check Number1 | EMPLID | Amount1 | Check Number2 | Amount2 |
101 | A101 | -100 | ||
103 | A103 | -300 | ||
102 | A102 | -200 | ||
102 | A102 | -200 | ||
101 | A101 | -100 | ||
102 | A102 | -200 | ||
102 | A102 | 200 | ||
103 | A103 | -300 | ||
103 | A103 | -300 | ||
103 | A103 | 300 | ||
103 | A103 | -300 | ||
104 | A104 | 400 | ||
104 | A104 | -400 | ||
104 | A104 | -400 | ||
105 | A105 | -500 | ||
106 | A106 | -600 | ||
106 | A106 | -600 | ||
106 | A106 | -600 | ||
107 | A107 | -700 | ||
107 | A107 | -700 | ||
107 | A107 | 700 |
Desired Output: -
Check Number1 | EMPLID | Amount1 | Check Number2 | Amount2 |
101 | A101 | -100 | ||
101 | A101 | -100 | ||
102 | A102 | -200 | ||
103 | A103 | -300 | ||
104 | A104 | -400 | ||
105 | A105 | -500 | ||
106 | A106 | -600 | ||
107 | A107 | 700 |
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'll take a look at it 👍
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Christina_H. Thanks for your instant reply.
Please help me on this issue because I have to deliver it asap.
Because of only this issue I am stucked otherwise it is a great solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
For anyone else following this, the problem is where there are multiple possible pairs that can be matched to reconcile. The unique tools end up removing too many records so not reconciling everything.
Unfortunately I can't come up with a solution that doesn't involve either a multi row formula tool or an iterative macro, both of which have been excluded by @vijaylnyadav's client.
The data can still be reconciled if necessary by running it through the original workflow multiple times until no more records are removed. All removed records are correctly reconciled, but not every reconciliation is picked up in a single run.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Christina_H,
By running multiple times the records are still not reconciled. Every time we are getting the same result.
Please suggest alternate solution if anyone has it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
One final attempt from me using tile tools. Those may still not be accepted by your client. I will also admit that I don't know a lot about using them, so I recommend some thorough testing.
By the way, for the original to work over multiple runs you need to change the input every time to the output of the previous run.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
HI @Christina_H thanks for your reply.
We had spoke to client for using multirow tool.
So we have to show some demo to them about this data.
So can you please help me to redesign the workflow using multirow tool.
This time seeing the issue we think client will accept multi-row tool.
Can you please help me on this.
Thanks for all your support.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Unfortunately I can't spend any more time working on this. My previous solution using tile tools may work for you, but I've provided two solutions now and can't justify spending more time on a third.

- « Previous
-
- 1
- 2
- Next »