Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: USD $60.00 Reconciliate two worksheet and display differences

  1. #1
    Join Date
    9th December 2016
    Posts
    3

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,276

    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.be ==> English articles ==> Excel memes

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

  3. #3
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,276

    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.be ==> English articles ==> Excel memes

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

  4. #4
    Join Date
    9th December 2016
    Posts
    3

    Re: USD $60.00 Reconciliate two worksheet and display differences

    Very good, thanks !

  5. #5
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,276

    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 Images
    Regards,

    Wigi

    Excel MVP 2011-2014

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

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

  6. #6
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,276

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Regards,

    Wigi

    Excel MVP 2011-2014

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

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

  7. #7
    Join Date
    9th December 2016
    Posts
    3

    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

  8. #8
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,276

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Regards,

    Wigi

    Excel MVP 2011-2014

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

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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: December 23rd, 2013, 19:10
  2. Compare Two Columns - Display List of Differences
    By corrinebean in forum Excel General
    Replies: 2
    Last Post: December 31st, 2010, 06:57
  3. Compare Two Worksheets And Display Differences
    By Shahidha in forum Excel General
    Replies: 4
    Last Post: May 9th, 2008, 12:54
  4. Compare 2 Sheets And Display Differences On 3rd
    By Niklas1976 in forum Excel General
    Replies: 2
    Last Post: February 14th, 2008, 01:31
  5. Compare Strings & Display Differences
    By anant.javali in forum Excel General
    Replies: 7
    Last Post: September 11th, 2007, 18:12

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno