2 column matching (reconciliation)

[iikarus]

Greetings everyone,
After scouring the internet for days now, and unable to find a solution to fit my needs, I present this question to you wise souls.

If a solution already exists on this forum, then please accept my sincerest apologies for reposting, as I was unable to find it using the search function. Majority of the solutions posted are either single column reconciliations, and/or have a unique key/identifier, both of which are not the available with my data.

I would really appreciate any guidance or help, either in the form of VBA, formulas, or conditional formatting

My data is as below:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]DEBIT[/TD]
[TD]CREDIT[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]-50[/TD]
[/TR]
[TR]
[TD]584[/TD]
[TD]-4[/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD]-4899[/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]4899[/TD]
[TD]-54[/TD]
[/TR]
[TR]
[TD]-11[/TD]
[TD]-358[/TD]
[/TR]
[TR]
[TD]-358[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]-100[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Salient features of the data are as follows:

1. The ordering of numbers in both the columns are mixed, they are neither ascending nor descending
2. There is no "key"or unique identifier which links the 2 columns.
3. The negative signs in the debit column, and the positive signs in the credit column are not mistakes, they are there on purpose.

Technicalities of the problem are as below:

1. If Debit + Credit = Debit, in this case the original debit becomes a reconciling item.
2. If Debit + Credit = Credit, in this case, the original credit becomes a reconciling item.
3. If Debit + Credit = 0, in this case, both the debit and credit entries are matched should be eliminated or deleted.
4. Please consider the above data to be present in the columns W and X in excel, with data present (several columns deep) on either sides of column W and column X

Based on the above, I was hoping if the solution could highlight the reconciling items on a cell level only, and/or delete the matching items on a cell level.

I hope that above would wet someones appetite for puzzle solving, which the rest of us could learn from.

Thanks,

 

[oldbrewer]

technicalities 1 and 2 lead to reconciling - do you want eg in first line of your data the answer to be "debit reconciled" or just debit

 

[oldbrewer]

or the 100 to be coloured red ?

 

[iikarus]

Thank you so much for your reply and help.

With regards to your first question, a text, "Reconciled" or highlighting in green color would be sufficient.

With regards to the second question, the below table can help.

Once again thank you so much for your guidance

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Debit[/TD]
[TD]Credit[/TD]
[TD]Classification[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]-50[/TD]
[TD]Reconciled - The cell for 100 should be highlighted green[/TD]
[/TR]
[TR]
[TD]584[/TD]
[TD]-4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]94[/TD]
[TD]-4899[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]150[/TD]
[TD]100[/TD]
[TD]Outstanding - The cell for 100 should be highlighted Yellow[/TD]
[/TR]
[TR]
[TD]4899[/TD]
[TD]-54[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-11[/TD]
[TD]-358[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]-358[/TD]
[TD]-100[/TD]
[TD]Reconciled - The cell for 100 should be highlighted green[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]-100[/TD]
[TD]Outstanding - The cell for 100 should be highlighted Yellow[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

why is 584 and -4 not reconciled ?

 

[iikarus]

Because there is no -584 to offset it in the credit column, nor is there a +4 in the debit column.

If you look at the first example for 100, the debit column in row 1 offsets with with row 7 of the credit column, as they are the first matches to offset each other.

Thanks once again for helping out

 

[oldbrewer]

so how are credit entries sometimes positive ? EAsy to see if debit entries are matched with an identical negative number, and why is the 4th 100 outstanding ?

 

[iikarus]

Positive credit entries and negative debit entries are basically
1-User error
2-System error
3-An entry which was passed by force, overriding system settings

The 4th 100 is outstanding because, the 1st debit +100 offsets with the first available -100 credit , and hence are deleted, or ignored, which leave us with +100 and -100 in the credit column.
Hope this clears the issue.

 

[oldbrewer]

so can you run a macro changing positive credit to negatives, and negative debits to positive. if not how can excel deal with this ?

 

[iikarus]

Not required, because these "issues" need to be highlighted in the matching process. Infact this is one of the reasons a reconciliation is done. The basic idea is:
3 + (-2) = 1

I have a excel file which manually goes through the entire process, which I can share with you if you like