mysticmoron1
08-04-2015, 03:56 PM
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!!!!
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!!!!