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]

so a positive credit needs to be highlighted, but NOT matched with a debit

 

[iikarus]

Absolutely.

Btw, I have an excel file which shows mannual workings, which could shed some light, could I send it to you please, if its not too much trouble

 

[oldbrewer]

that is not allowed under forum rules but you can make a google sheet open to anybody

in the credit column conditionally format all cells with =A1=>0

 

[iikarus]

Getting an error message, in the conditional formating about formula.

Tried typing by hand, and no numbers are highlighted, I think im doing something wrong

 

[oldbrewer]

in a new spreadsheet put -5 in cell A1, highlight A1, format, conditional formatting, cell equals and in next box <0

choose red ok A1 should be red now change A1 to 7 the red should disappear

 

[iikarus]

Sorry for the late response, nasty flu.

Not sure I follow the above, how will this work with multiple values, for example two 100's in the debit side, and five -100 in the credit side.

Atm I am trying to make a flowchart which should help.

 

[oldbrewer]

I am trying to establish if you know how to use conditional formatting - once A1 turns on and off correctly we can get back to your specific issue