+ Reply to Thread
Results 1 to 3 of 3

Help writing macro to reconcile two data sets and produce a results table

  1. #1
    Registered User
    Join Date
    01-25-2018
    Location
    New York
    MS-Off Ver
    2010
    Posts
    2

    Post Help writing macro to reconcile two data sets and produce a results table

    Hi All,

    I'm very new to writing excel macro VBA code and looking for some help to write a macro that will reconcile two data sets (by four variables) and then producing an output tab of the results, the data sets I'm trying to reconcile are below:

    Data Set 1

    Customer Name ID Amount Direction Date
    Customer 1 1234567 1000 Outgoing 1/9/2018
    Customer 1 1234567 1250 Incoming 1/10/2018
    Customer 2 7654321 1000 Incoming 1/11/2018
    Customer 2 7654321 1250 Outgoing 1/12/2018


    Data Set 2

    Date ID Name Amount Direction
    1/9/2018 1234567 Customer 1 500 Outgoing
    1/9/2018 1234567 Customer 1 250 Outgoing
    1/9/2018 1234567 Customer 1 250 Outgoing
    1/10/2018 1234567 Customer 1 750 Incoming
    1/10/2018 1234567 Customer 1 250 Incoming
    1/10/2018 1234567 Customer 1 250 Incoming
    1/11/2018 7654321 Customer 2 1000 Incoming
    1/12/2018 7654321 Customer 2 1250 Outgoing



    Results Data Set

    Date ID Customer Name Reconciled Amount Direction
    1/9/2018 1234567 Customer 1 1000 Outgoing
    1/10/2018 1234567 Customer 1 1250 Incoming
    1/11/2018 7654321 Customer 2 1000 Incoming
    1/12/2018 7654321 Customer 2 1250 Outgoing


    So basically I need to macro to match the two data sets by Data, ID, Direction and Amount, however, in data set 2 the amount needs to sum multiple lines to equal one line in data set 1. Once the macro matches the two data sets, I would need a third data table produced that show the results of the macro and identify if any lines dont match then to send the results to an Unreconciled tab.

    Is this possible using Macro VBA? Please let me know of any thoughts or ideas on how to write this macro!

    Thanks!
    iMaq_23

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Help writing macro to reconcile two data sets and produce a results table

    Hello iMaq_23,

    Welcome to the Forum!

    What information do you want on the un-reconciled tab?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    01-25-2018
    Location
    New York
    MS-Off Ver
    2010
    Posts
    2

    Re: Help writing macro to reconcile two data sets and produce a results table

    Hi Leith,

    Thanks!

    Basically, if the two data sets do not match, I would want the same information in the Results data set to be produced in the Un-reconciled tab:

    Date ID Customer Name Amount Direction

    If there are any un-reconciled items, which most likely wouldn't be any.

    Thanks,
    Moeez

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 09-06-2016, 04:59 AM
  2. [SOLVED] Sort macro conflicting with formula to produce incorrect results
    By turtlesrun in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2015, 08:56 AM
  3. [SOLVED] Double-click macro to produce different results based on different cell ranges
    By Hobsons in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-06-2013, 07:28 AM
  4. (SOLVED) Reconcile two sets of data
    By damilaredavids in forum Excel General
    Replies: 6
    Last Post: 12-31-2011, 05:39 AM
  5. Replies: 0
    Last Post: 12-02-2011, 01:02 PM
  6. writing a macro to produce data for different intervals
    By fodeps in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-23-2008, 06:18 AM
  7. reconcile 2 data sets with multiple variables
    By Brigitte in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-07-2007, 04:53 PM

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