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]

I think I solved one part of the issue. I am attaching the sample workflow I did. Now to work on the weird uneven rows of data. If anyone is up for a fun challenge, help a girl out please. Thanks in advance.

[Qiu]

@sowjanyayinti 
This problem looks interesting and yet challenging. Just want to confirm, when you say "Split"? 
Do we only split to two amount, or could be any number of split?

5936: -2894 and  -3042 (two split only)
or could be 5936: -2890, -3040 and -6?

[caltang]

Need a bit more context. Like @Qiu asks, what are the scenarios? Will it always have a match? Or will it sometimes not?

[sowjanyayinti]

@Qiu it can be two or three or sometimes more than 5 :). I am attaching a new test file with such examples. Especially the last one in the input tab, if you notice, some are exact matches and some are not for same name and id.

I thought i was able to solve it only to find myself back to square one :). Thank you so much for looking into it.

[sowjanyayinti]

No takers? :)

[caltang]

I think these codes are supposed to be the same right? IJK888? 

[Qiu]

@caltang 
I think @sowjanyayinti  mentioned that " some are not for same name and id.", which makes this one really challenging.
I have spent some time and not going anywhere with my extended sample data.
As least we should limite the situation that the match only happens for same ID. 😁

[caltang · Accepted solution]

I am assuming a few things:

This works if the total amounts tally and match. Where I think it falters is when the amounts do not match in their sum totals for those which are of the same Name and ID and varying amounts of negatives and positives.

We need to see a scenario where the total does not tally, how do you want to handle that via Excel?

[caltang]

Oh? Those codes are meant to be different? If they are then it's much harder... @Qiu 

Because how would we know to match to the first value's ID and Name if the other IDs are slightly off?