2 column matching (reconciliation)

iikarus

New Member
Joined
Jan 1, 2019
Messages
8
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:

DEBITCREDIT
100-50
584-4
94-4899
150100
4899-54
-11-358
-358-100
20-100
2050

<tbody>
</tbody>

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,
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0
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

DebitCreditClassification
100-50Reconciled - The cell for 100 should be highlighted green
584-4
94-4899
150100Outstanding - The cell for 100 should be highlighted Yellow
4899-54
-11-358
-358-100Reconciled - The cell for 100 should be highlighted green
20-100Outstanding - The cell for 100 should be highlighted Yellow
2050

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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.
 
Upvote 0
so can you run a macro changing positive credit to negatives, and negative debits to positive. if not how can excel deal with this ?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top