I have to define the workflow for reconciliation please help me in this.
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.
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 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.