+ Reply to Thread
Results 1 to 3 of 3

Supplier Reconciliation, different levels of matching

  1. #1
    Registered User
    Join Date
    07-30-2010
    Location
    ASIA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Supplier Reconciliation, different levels of matching

    Hello All:

    Please note, I have read all the subjects named reconciliation and it was very good but they wouldn't help me do what I need.

    I have to deal with long number of lines and I have to match them to similar lines from other companies, both lines have dates and amounts, sometimes both have Invoice number or one have invoice and the other has a description that has the invoice number.

    I want to ask for help and I "tried" to make the macro idea useful in all weird cases, the charts I get are very complicated and random and of course I will make sure to arrange them as the example I am attaching, making sure that column 1,2,3,4 are the same always.

    I get two types of statements;

    The First Case:

    what I am looking for is a way to send the matching results to a new page were they descend from 100% matching to less matching, for example the first 10 lines will be 5 lines matches to 5 lines were the invoice number from my company (DOC#) was found in the description line of the supplier sheet and the date & amount matches also.

    Then another 10 lines were matching happened in invoice and DATE category only

    Then another 10 lines were matching happened in invoice and AMOUNT category only.

    Then another 10 lines were matching happened in DATE and AMOUNT only.

    The non-matching lines can be left in the original sheet.


    Then matching for Debit memos & credit memos based on DATE and AMOUNT

    non-matching can be left


    Then matching for Payment based on AMOUNT (first amount with first amount if possible)

    non-matching can be left


    The Second case:

    The RECORD# in supplier sheet is sometimes DOC# number, so how do I do change the macro to search in this column instead of DESCRIPTION.

    Since I have never done coding am not sure if this is the best way to think about it.

    Notes:

    The number of columns is not fixed and different in the 2 sheets and I want to cut the whole row.

    Columns 1,2,3,4 are fixed with different name for column 3 in Supplier [RECORD# (case 1) or DOC# (case 2)]

    I hope you get the idea and PLEASE tell me if you find a flow in this idea ????

    I hope its easier done than said....

    Please check the attched I have 2007 but did this on 2003
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    03-30-2011
    Location
    Coventry, UK
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Supplier Reconciliation, different levels of matching

    Hi, I am working in very similar stuff.

    I'd suggest you to explore pivot tables aproach rather then getting results selected to different spreadsheets.

    search in internet for "compare 2 lists in excel", then create a page just to make the comparisons and some other results you want to get from them.

    Ultimatelly create pivot table reports based on the comparing table.

    special tip: create pivot tables based on separate lists, put them together then use normal excel formulas to have further report results.

    hope this helps

  3. #3
    Registered User
    Join Date
    07-30-2010
    Location
    ASIA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Supplier Reconciliation, different levels of matching

    Thanks kafukalatrava, I need to find a job now :/

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1