Reconciliation Macro Help

[mysticmoron1]

Hello All, I am new here but will be very active over the next coming months both on a requesting and solving basis.

At any rate, thank you for having me here.

Now on to my request.

What I need is a macro that reconciles two different sets of data. There is no readily available unique identifier to isolate each individual entry in either dataset so the best we can do is use a concatenation or reconcile based on a "net premium" value. Lets write this macro based on the "net premium" value.

Lets call the first data set "dataSet A" and the second data set "dataSet B".

What I would like is to place dataset A on the left side of one worksheet (say Columns A:M) and then leave 3 blank columns and then dataSet B on the right side of the same sheet. Say columns (Q:AC). In the middle blank column I would like to have a formula which subtracts the balance in say M2 from the 'corresponding' balance in dataSet B. Say AC2. Ultimately this equation should equal NIL. After we have found the entries on either side which reconcile out and therefore NIL out, I would like to leave those at the top of the worksheet. below the matching balances I would like to place all the entries which do not tie out. I.e., the reconciliation breaks.

Now, for the reconciliation breaks, there are entries in both data sets which cancel themselves out. WHat I mean is that there is a positive transaction and a negative transaction for the exact same amount in opposite directions. For those I would like to group them together on their respective sides of the reconciliation and highlight the sums that cancel each other out.

After that is done, we will be left with the REAL reconciliation breaks. And I would like these to be placed below the reconcilied items and the canceled items.

hopefully i have explained this well enough but if you require further details please let me know.

Cheers!!!!

[Aussiebear]

Would be really helpful if you were to post a workbook showing sample data in a before and after scenario, as this helps negate any confusion. To attach a sample workbook, click on go advanced, scroll down to manage attachments and follow the prompts from there.

From my initial reading, I am somewhat confused by the intention as stated in the paragraph starting "Now, for the reconciliation breaks..... Since if either side of the equation cancel each other out, what's the need to further examine the data?

[mysticmoron1]

Sorry about that. Here you go.

I want all of this to be done in one workbook. Lets say the threshold (if possible to implement into macro) for a match is 0.25. So, if the difference between two cells is less than 0.25 then it is considered a match.

Please let me know if I need to further explain anything. I have attempted this myself but have failed miserable as I know a lot of theory but my execution is lacking hence why I need to see some actual code and try to replicate.

THank you!!

[Aussiebear]

Sorry for the delay, but have been on nightshift.... Just had a quick look at your data sets, and was wondering how to implement any rules you might have.

1. Matching of names. I see 3 examples of how you data will fail a name match rule; Brett Favre - Favr Bretto, Charles Barkley - Barkley Charles, Lebron James - Jame Lebron, and Scottie Pippen - Pippen Scottie (repeat example). How's the workbook to know that these are the same people?

2. Matching of Amounts. Whilst I understand the concept of accepting as a match a value that falls within plus or minus 0.02 of the initial value, how does the workbook understand if some values being matched are both plusses or both minuses from either side of the data sets?

[mysticmoron1]

I think it might be possible to do a concatenation on two sets of the data to form a unique identifier.

What I would want to happen is find theunique identifier (via the concatenation) from datasetA. Locate that identifier in datasetB and then place the two sets of data on the same row with space in between to be able to find the difference.

Ideally, the difference will be NIL but even if it is off by a few cents it would still be classified as a match.

In a nutshell, I guess what I am looking for is a program which will find a value from datasetA, locate that same value in datasetB. then put the two data entries on the same row and use a formula to find the difference between the two values associated with the unique identifiers.

[Aussiebear]

Who applies the identifier?