Credit or Debit(Contras) ledger transactions from rows to columns

[sowjanyayinti]

Hard to explain it in a single title but here it goes. I am attaching sample file with input and output tabs. I tried playing with Tile tool, cross tab and transpose tools for far too many hours but not getting what i want. I am pretty sure i am making some silly mistakes. Can anyone help?

It basically needs to take the credit and debit rows and make a single row of credit and debit column as shown in the output. If the amounts do not match exactly then it should split it to match as shown in the first example in the input file

[sowjanyayinti]

Sorry. Yes. They should be the same. Bad copy paste on excel. See why I don't like excel and love alteryx ;)

[sowjanyayinti]

@Qiu  and @caltang  - Thank you so much. So the way analysts are doing it manually in excel is - They see three rows - one with -2894, 2nd with -3042 and they add it in debit and comes to 5936 so they then add it like I had in the 'Output' tab manually. They basically are manually doing this... (Which I am not a fan of... but...)

Let me check @caltang's solution adn will get back to you guys.

[sowjanyayinti]

This is the solution. Thank you @caltang . This workflow will be run only on those records that tally to a 0 (Contras). Those that do not tally to a zero won't even reach this far to get this workflow run on them :).

Thank you so much again for your help @caltang and @Qiu.

[Qiu · Accepted solution]

@sowjanyayinti 
If it is one to one match, I think we can manage it.

Thank you for letting know if it works somehow. 😁