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

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?

For sowjay.yxzp

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

Thank you for letting know if it works somehow. 😁

Contra Single Match test-r1.yxmd

All comments

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.

Contra Single Match test.yxmd

@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?

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

@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.

Test.xlsx

No takers?

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

@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. 😁

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?

@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.