1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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

Discussion in 'VBA Macros' started by Monty, Jul 17, 2017 at 8:12 PM.

  1. Monty

    Monty Well-Known Member

    Messages:
    758
    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.

    Attached Files:

    Last edited: Jul 17, 2017 at 8:22 PM
  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    What's the expected output for your example?
    Monty likes this.
  3. Monty

    Monty Well-Known Member

    Messages:
    758
    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.
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    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?
    Monty likes this.
  5. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Also, what should happen in following instance.

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

    Monty Well-Known Member

    Messages:
    758
    Sorry I prepared data in hurry both are USD.
  7. Monty

    Monty Well-Known Member

    Messages:
    758
    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.
  8. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Ok, I think I got the general idea.

    What version of Excel do you have? Specifically, do you have access to PowerQuery/"Get & Transform"?
    Monty likes this.
  9. Monty

    Monty Well-Known Member

    Messages:
    758
    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..
  10. Monty

    Monty Well-Known Member

    Messages:
    758
    Thanks a lot for working on this...
  11. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Will Cost Center + Sub A/C produce unique? Or do I need to consider "Status" on top of it to make it unique?
    Monty likes this.
  12. Monty

    Monty Well-Known Member

    Messages:
    758
    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.
  13. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    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.
    Monty likes this.
  14. Monty

    Monty Well-Known Member

    Messages:
    758
    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.
  15. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Can you upload file with more sample? Your uploaded sample doesn't accurately reflect your scenario.
  16. Monty

    Monty Well-Known Member

    Messages:
    758
    Sure enough will come up with proper dataset tonight...Thank u
  17. Monty

    Monty Well-Known Member

    Messages:
    758
    For a quick start as you suggested to concatenate

    Code (vb):
    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: Jul 18, 2017 at 8:23 PM
  18. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    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?
  19. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,316
    Try this as starting point.
    Code (vb):
    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
    Monty likes this.
  20. Monty

    Monty Well-Known Member

    Messages:
    758
    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: Jul 19, 2017 at 9:02 PM

Share This Page