Using Iterative Macro to Add Values in Column to Yield a Value in Another Table

Question

Hi,

I am trying to match cartain amounts in one table to another table when there is no reference between the two tables to link the numbers up. I figure that using an Iterative Macro would be the best route to go but I don't know how to go about setting it up.

Transaction Date Amount 8/12/2021                 34.178/26/2021               117.008/6/2021               130.238/11/2021               270.008/2/2021               351.238/10/2021               468.008/3/2021               750.008/27/2021               758.358/11/2021               849.508/6/2021               920.008/5/2021               950.008/2/2021           1,875.008/3/2021           2,043.758/5/2021           2,563.748/11/2021           3,073.168/13/2021           3,391.058/10/2021           3,915.008/6/2021           3,997.418/12/2021           4,234.198/5/2021           4,265.008/12/2021           4,280.648/11/2021           5,271.008/13/2021           6,375.008/13/2021           6,750.008/2/2021           8,129.368/12/2021           8,400.008/30/2021           8,481.008/2/2021           8,500.008/2/2021           8,954.528/6/2021           9,339.498/10/2021           9,475.008/13/2021           9,975.008/4/2021         11,362.008/5/2021         13,500.008/2/2021         14,875.008/9/2021         15,030.998/13/2021         15,250.008/9/2021         15,450.008/18/2021         17,324.948/2/2021         18,342.508/3/2021         23,580.428/6/2021         24,250.008/4/2021         24,475.008/4/2021         25,144.948/6/2021         27,053.008/11/2021         30,683.048/6/2021         31,600.008/2/2021         40,300.008/2/2021         41,015.008/6/2021         41,230.028/4/2021         46,583.188/2/2021         47,250.008/5/2021         51,480.008/3/2021         53,915.168/11/2021         59,076.998/2/2021         66,432.618/13/2021         69,050.008/10/2021         72,314.168/13/2021       111,366.338/3/2021       132,600.008/13/2021       458,766.00Total   1,728,189.07

and

Posting DateAmount8/31/2021             (604.70)8/31/2021           9,961.058/26/2021         17,324.948/19/2021   1,701,507.78Total   1,728,189.07

Notice how both the totals equal eachother but I'm trying to figure out which rows in the first table match with which numbers on the latter table.

Thanks,

Alex

DawnDuong · Answer

hi @alexlat11

It appears that you are trying to reconcile the GL to some manual management adjustments.

This is not reconcilable based on the available information - for instance, you can see that there is no negative value in the GL export, but there is a negative value in the manual management adjustments.

I think you will need to enquire / obtain the breakdown for the summary table and/or understand how the data flows between different financial data sources first.

Dawn.

mst3k · Answer

i think you're going to run into 1 problem since one of your totals has a negative number but none of the original values is negative?

the only way i can think to do this efficiently would be to treat it like knapsack problem and use the optimization tool. however i'm not really sure how to set up the optimization tool for a knapsack problem..