Reconcilation Logic

[vijaylnyadav]

Hi, Please help me to build the logic as I am unable to find the logic.

Input Data: - 

Check Number	EMPLID	Check Date	Amount
101	00000833839	01-08-2017	-201.02
103	00000858235	01-11-2017	372.36
102	00000856266	01-08-2017	-1,064.40
103	00000858235	01-11-2017	-372.36
105	00000865877	01-08-2017	-392.32
104	00000865876	01-01-2018	420.12
102	00000835358	01-08-2017	-1,064.40
104	00000865876	01-01-2018	-420.12
105	00000865877	01-08-2017	392.32
101	00000833839	01-08-2017	201.02
103	00000858235	01-11-2017	-372.36
104	00000865876	01-01-2018	-420.12

Condition: - 

If same check number have same amount with different sign then it is reconciled and it is deleted from data. It should be 1 to 1 matching.

here I cant use multirow tool to achieve this.

Is there any other way to achieve.

Desired Output: - 

Check Number	EMPLID	Check Date	Amount
102	00000856266	01-08-2017	-1,064.40
102	00000835358	01-08-2017	-1,064.40
103	00000858235	01-08-2017	-372.36
104	00000865876	01-01-2018	-420.12

[Jean-Balteryx]

Hi @vijaylnyadav ,

Here is a workflow doing what you need.

Basically it creates a column with absolute amount to sort the data then using a Multi-Row Formula tool it adds a fiel telling ifthe row needs to be removed based on whether the row before or after is the negative of it.

[vijaylnyadav]

Hi @Jean-Balteryx  thanx for your feedback.

Here, I cant use multi-row formula due to some business requirements.

Do we have alternate solution for this to achieve?

[Jean-Balteryx]

I can't think of one right now, I'll see if i can find one. Maybe @atcodedog05 can help you with this !

[HomesickSurfer · Accepted solution]

hi @vijaylnyadav 

This works.  'Like' and 'Accept as Solution' if you agree.

[atcodedog05]

Nicely done @HomesickSurfer 🙂 Maybe we were just over complicating stuff 😅

I thought of summarize but left out to groupby date.