Announcement

Collapse
No announcement yet.

USD $60.00 Reconciliate two worksheet and display differences

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • USD $60.00 Reconciliate two worksheet and display differences

    6$ paid to ozgrid (transaction ID 9MV140662P067244U)

    I would need a macro in the Reco.xlsx file that would open the workbooks Invoices.xlsx

    Then, the macro should:
    - Filter column A of the invoices to take only "Invoice" entries
    - Match the column Account from Payments worksheet with the column Account from Invoices.xlsx
    - Match the column SubAccount from Payments worksheet with the column SubAccount from Invoices.xlsx (the format is slightly different: the account starts with xxx in the Invoices spreadsheet )
    - Compare the column Amount from Payments worksheet with the column Amount from Invoices.xlsx (a tolerance of 10% should be accepted, i.e 6,4 on the one hand and 6,7 on the other hand should be considered as matched)

    => Matched items should be displayed side by side on the reco.xlsx spreadsheet as shown
    - Any entry in one sheet that cannot be matched on the other sheet should appear in reco.xlsx as unmatched
    Attached Files

  • #2
    Re: USD $60.00 Reconciliate two worksheet and display differences

    Hi

    I will have a look at this one and get back to you.
    Regards,

    Wigi

    Excel MVP 2011-2014

    For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

    Comment


    • #3
      Re: USD $60.00 Reconciliate two worksheet and display differences

      Okay it's clear. I will write the code.
      Regards,

      Wigi

      Excel MVP 2011-2014

      For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

      -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

      Comment


      • #4
        Re: USD $60.00 Reconciliate two worksheet and display differences

        Very good, thanks !

        Comment


        • #5
          Re: USD $60.00 Reconciliate two worksheet and display differences

          Hello

          I finished the code. I will send you a PM for the PayPal payment and upon receiving the funds I will upload my code.
          It's almost 2 AM here in Belgium so depending on how fast the payment is done, I could upload the solution tomorrow morning.
          Attached Files
          Regards,

          Wigi

          Excel MVP 2011-2014

          For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

          -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

          Comment


          • #6
            Re: USD $60.00 Reconciliate two worksheet and display differences

            Hi,

            Thanks, funds received. Attached my coding. Can you give a try please ?
            2 remarks:
            - press Alt-F8 to go to the macro's. The workbook contains only 1 macro.
            - I removed the first empty row in the invoices sheet, and also some other empty rows/columns. This makes the code easier.
            Please also make sure the first empty row in the Invoices sheet is deleted before running the macro.
            Attached Files
            Regards,

            Wigi

            Excel MVP 2011-2014

            For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

            -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

            Comment


            • #7
              Re: USD $60.00 Reconciliate two worksheet and display differences

              Hello

              Many thanks, it looks very good!
              I have some minor remarks:
              - I would like to be able to keep some rows on the top (I cannot really change input files)
              - the macro does not seem to reconcile the line 979.4€ from Invoices with the 970€ amount from the Payments sheet, although amounts are well within the 10% tolerance

              Comment


              • #8
                Re: USD $60.00 Reconciliate two worksheet and display differences

                Hello,

                Okay, your call. But then range("A2") (which is cell A2) on the Invoices sheet is hardcoded in the VBA-code.
                I changed that one.
                Also, that 970 is matched now (probably it did not match if you have a blank row and did not change for Range("A2").
                Attached Files
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment

                Working...
                X