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
Bookmarks