Reconciliation of two sets of data

Hello,

I am hoping you can help.

I have two sets of data that I pull into a spreadsheet.
Data1 and Data2.

I am looking to create a macro, which recs the two sets of data and spits out only errors.

I have attached an example spreadsheet and coloured in examples of various errors I might come across.

The data should be reconciled using a concatenation of ACCOUNT and REFERENCE.

Example 1 (highlighted in green) - Data1 has Value 1 as -226, however, Data2 has Value 1 as -225

I would highlight this as an error on the Rec sheet as Incorrect Value 1 and display the Account, Reference and the Data2 Value 1

The procedure would then run through again and notice Data1 has a Value 2 as -229, however, Data2 has a Value 2 as -228.

I would highlight this as an error on the Rec sheet as Incorrect Value 2 and display the Account, Reference and the Data2 Value 2

Example 2 (highlighted in orange) - Data1 has a CCY of GBP, however, Data2 has a CCY of EUR

I would highlight this as an error on the Rec sheet as Incorrect CCY and display the Account, Reference and the Data2 CCY value

The same would apply to date etc.

If the Account & Reference concatenation cannot be found on the Data2 sheet an error should be displayed advising the Account and Reference, which is missing. (highlighted in yellow)


Is anyone able to help?

Thanks

Hello mccrimmon,

The following macro has been added to the attached workbook. The original reconciliation sheet "Rec" has been named "Rec1". I did this to compare the macro results against your original data. A button has been added on the "Rec" worksheet to run the macro.

Please Login or Register  to view this content.

Excellent work Leith.

Thanks very much.
Makes a lot more sense to see it written out like that.

I've seen examples before and struggled to get my head round it.

Hi Leith,

Wondering if you can assist me further.
I am looking to complete a number of recs and have all the results on the Rec sheet.

How do I get it so that when I complete the first reconciliation, I can go and complete a further reconciliation between Data3 and Data2 and have the results added to the end of the previous Rec like the attached example?

Thanks again
Much appreciated

Hello mccrimmon,

Are you saying that after Data2 is reconciled against Data1, you what to reconcile Data3 against Data1 and record the errors on the Rec sheet?

Originally Posted by Leith Ross

Hello mccrimmon,

Are you saying that after Data2 is reconciled against Data1, you what to reconcile Data3 against Data1 and record the errors on the Rec sheet?

Sorry, the sheet names probably confuse matters.

After reconciling Data1 against Data2 and recording the errors on the Rec sheet, I want to rec Data3 against Data2 and record the error at the end of the previous errors on the Rec Sheet.

Thanks

Hello mccrimmon,

If an error is reported while reconciling Data1 and Data2 that makes sense because we know Data1 is the master file. If there are now 2 master files then how do you know where the error is?

In your last post the reference BM3-REC3example doesn't exists on Data1 and would be reported missing. Reconciling Data2 and Data3 reports a currency error. Since we know Data2 is the sheet being reconciled, how due 2 errors from 2 different "masters" help the reconciliation process, since there is no record of which sheet the error occurred on?

Originally Posted by Leith Ross

Hello mccrimmon,

If an error is reported while reconciling Data1 and Data2 that makes sense because we know Data1 is the master file. If there are now 2 master files then how do you know where the error is?

In your last post the reference BM3-REC3example doesn't exists on Data1 and would be reported missing. Reconciling Data2 and Data3 reports a currency error. Since we know Data2 is the sheet being reconciled, how due 2 errors from 2 different "masters" help the reconciliation process, since there is no record of which sheet the error occurred on?

Hi Leith,

Thanks again for getting back to me.

I have updated the spreadsheet to make it easier to understand as the naming convention of adding new sheets etc is probably confusing matters.

Basically there is three different account types, which need to be reconciled against the master list.

All errors should be reported on the Rec sheet, however, I take on board your comment and believe the rec name should probably be added to the first cell of the rec to show what sheet it has come from?

Hopefully the new attachement makes more sense.

Thanks

McCrimmon

Originally Posted by snb

or:

Please Login or Register  to view this content.

Thanks for sharing your solution.

The code is extremely fast, however, I really need all the errors written out for the user as there is potentially thousands of accounts that need reconciled so do not want the user having to scroll through each sheet to ensure there are no errors.

Please Login or Register  to view this content.

Originally Posted by snb

Please Login or Register  to view this content.

Looks like theres an error in the code.
Receving a syntax error on this line of code.

Please Login or Register  to view this content.

Is it possible to explain what each part of the code is doing?

I am trying to learn VBA so the more I can learn from this forum the better.
Leiths original was very useful and has helped me understand what its doing.

Please Login or Register  to view this content.

Originally Posted by snb

If jj <> 2 Then c01= c01 & "|" & IIf(sn(j, jj) <> sm(.Row, jj + 2 + IIf(jj = 4, 1, IIf(jj = 5, -1, 0))) Or jj = 3, sn(j,jj), "")

Now receiving an Object error on the below:

Please Login or Register  to view this content.

Originally Posted by snb

Please Login or Register  to view this content.

424 object required error on this now

Please Login or Register  to view this content.

Any suggestions?

Originally Posted by snb

Buy some spectacles....
.Cells(1, 1).Resize(UBound(Split(c01, vbr)) + 1) = worksheetfunction.Transpose(Split(c01, vbCr))

Apologies, my bad.

However, I have run your code but all it does is create a new sheet and populates the first reference and values.

I dont think its what I am after.

Thanks for your help, its really appreciated, however, im really looking to learn from this aswell as manage the solution and I am not understanding any of what you have provided.

Thanks again though.

Hello Brian,

The macro now works will all the account sheets. Each account sheet is checked using the Transaction Journal entries from the "Master" worksheet. All errors found in an account and reference line item will be logged to the "Rec" sheet. Each entry is now accompanied by the name of the worksheet. Here is the amended macro and workbook containing it.

Please Login or Register  to view this content.