• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Reconciliation between two systems

Monty

Well-Known Member
Hello Everyone.

This is regarding on the reconciliation file which have multiple condition to match...tried with looping through and auto filtering but no success.

I have data which comes from two different systems which we can not be change either helper columns or interchange columns to simplify it.

The data size would be around 50k...and taking 8 hrs to do the rec between two sheets.

So basically question is..Need to compare two sheets based on some parameters..

Attached file with complete notes.

Please do need full.
 

Attachments

  • Rec.xlsb
    25.8 KB · Views: 8
Last edited:
Hello Chihiro..

Hope you are doing good.

There is no output for this reconciliations actually..

first need to pic the sub-account with it's cost center, currency , status and check with the other system that is SYSTEM 2 Tab.

need to compare with all the parameters then finally check if the amount is also matching if matching then it is fine...if not need to over wright the amount which is in the System 1 tab..

Hope am not confusing you..
Please let me know any questions on this...going mad thinking about this from the past one week.
 
Well... you have USD.530.680268 & HKD.20.00 in Narrative.

Amount is USD 529.832 & USD 10.804 respectively in System 1.

What should be used in Amount column? Both isn't a match, but one uses HKD instead of USD. What should happen then? Do you use fixed exchange rate? Or should output update both CCY & Amount?
 
Also, what should happen in following instance.

If an item is found in System 2, but not in System 1, or vice versa.
 
Well... you have USD.530.680268 & HKD.20.00 in Narrative.

Amount is USD 529.832 & USD 10.804 respectively in System 1.

What should be used in Amount column? Both isn't a match, but one uses HKD instead of USD. What should happen then? Do you use fixed exchange rate? Or should output update both CCY & Amount?
Sorry I prepared data in hurry both are USD.
 
Also, what should happen in following instance.

If an item is found in System 2, but not in System 1, or vice versa.

We are only comparing System 1 to 2 only...if it matches all parameters excepect amount which is mostly closer amount are same...if amount differ then need to overwrite that amount.
 
Ok, I think I got the general idea.

What version of Excel do you have? Specifically, do you have access to PowerQuery/"Get & Transform"?
 
Hey Chihiro...we are using 2013 at work without any addins like power query or pivot unfortunately...

So wanted to manage by loops an autofilter..
 
Will Cost Center + Sub A/C produce unique? Or do I need to consider "Status" on top of it to make it unique?
 
The answer is no...

We have to autofilter first with Sub account and pick up the first cost center and also store it's currency, amount and status in a variable and start checking with the system 2 tab and come back to system 1 tab and with the same filter go with second cost center based on number of cost center available.
 
Hmm... is there combination of columns in System 1 where it will produce unique list that can be matched with something available in System 2?

Ex: Cost Center & Sub A/C & CCY & Status?

With your sample data, there really isn't a way to do comparison without somehow generating unique identifier that can relate the two systems.

Edit: I'm stepping out for the night. Will take a look tomorrow more in detail.
 
Thanks for the time Chihiro.

There is no unique combination i can think of..

We have to auto filter first with Sub account and pick up the first cost center and also store it's currency, amount and status in a variable and start checking with the system 2 tab and come back to system 1 tab and with the same filter go with second cost center based on number of cost center available.


Waiting for you response...Have a great evening.
 
Can you upload file with more sample? Your uploaded sample doesn't accurately reflect your scenario.
 
For a quick start as you suggested to concatenate

Code:
Sub Test()
Dim Sys_1 As Worksheet
Dim Sys_2 As Worksheet
Dim Lrow As Long
Dim Concat As String

Set Sys_1 = Worksheets("SYSTEM1")
Set Sys_2 = Worksheets("SYSTEM2")


Lrow =  Sys_1.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To Lrow

    'Creating concatination from SYSTEM 1 SHEET  TO COMPARE TO SYSTEM2 SHEET.
      Concat = Sys_1.Range("G" & i).Value & "|" & Range("I" & i).Value
     
    Next i

End Sub
Hello Chihiro

I just spoke to the business and tried to customized the requirement not many changes

***No need to compare the currecy type that is USD or GBP now..little work is reduced.

***I suggested them in SYSTEM 2 as an output against each row in blank column i will put a comment with the help of macro stating "Amount not matching" or Cost center not matching or, Status not Matching so on..

As you suggested we can concatenate Cost center and Sub account for a quick check between two sheets.

Now trying to put all together below with one example.

System1

1) Filtered with Sub A/c (H column) with 100.

2) i can see there are two cost center under sub account 100

Cost center
00110590
00110550

Now pickup the first cost center that is 00110590 and also remember amount and Status to compare with System 2 data.

now we have cost center 00110590, amount 529.832 and Status : Accrual


System 2

1) Filtered with Sub A/c (H column) with 100.


2) i can see there are two cost center under sub account 100

Cost center
110590
110550

need to check if the cost center in the system 1 is matching with system 2.
need to check if the status in the system 1 is matching with system 2.
need to check if the Amount in the system 1 is matching with system 2.

Challenging part is Status amount is "G" column in system 2 which is long string from which we need to see status and amount matching.


Let me know any questions..Still trying to make things easy for myself to get ride of this.
 
Last edited:
I've got the basics done. But need final piece of info.

How close do Amount need to be to be considered match?

Does it need to be exact match? Or to 3rd decimal enough?
 
Try this as starting point.
Code:
Sub Demo()
Dim s1dic As Object, s2dic As Object, s1Key, s2Key
Dim s1Arr, s2Arr
Dim s1kStr As String, s2kStr As String

s1Arr = Sheets("System 1").Range("A1").CurrentRegion.Value

Set s1dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(s1Arr, 1)
    s1kStr = s1Arr(i, 7) & "|" & s1Arr(i, 8) & "|" & s1Arr(i, 6)
    s1dic(s1kStr) = s1Arr(i, 5)
Next

s2Arr = Sheets("System 2").Range("A1").CurrentRegion.Value

Set s2dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(s2Arr, 1)
    s2kStr = "00" & s2Arr(i, 1) & "|" & s2Arr(i, 2) & "|" & Split(s2Arr(i, 7), "-")(0)
    tempStr = Split(s2Arr(i, 7), "-")(4)
    tempStr = Right(tempStr, Len(tempStr) - 7)
    s2dic(s2kStr) = CDbl(tempStr)
Next
For Each s1Key In s1dic.Keys
    If Not s2dic.Exists(s1Key) Then
        s1dic(s1Key) = "Status does not match"
    Else
        If Round(s1dic(s1Key), 3) = Round(s2dic(s1Key), 3) Then
            Debug.Print
            s1dic(s1Key) = "Match"
        Else
            s1dic(s1Key) = s2dic(s1Key)
        End If
    End If
Next

Sheets("System 1").Range("I2").Resize(s1dic.Count) = Application.Transpose(s1dic.Items)

End Sub
 
Hello Chihiro

Thank you so very much for the code and your time.

Let me give a try at work.. will surly get back to you with some changes as trying to make more simply the process..
 
Last edited:
Back
Top