Sum value combinations in a column to see which equal a certain value

Hello everyone,

I have a that contains multiple customers and their transactions. For each customer, I have a certain amount I am trying to identify which transaction amounts add up to that specific amount. I tried looking online and some posts state to use the "Optimizer" tool, but I can't figure out for the life of me how to configure it for my purpose or if there was an easier way of going about it in Alteryx.

I attached a test file. You will see there are two customers, "Apples" and "Oranges". In column K I have the target amount for each customer (Apples = 206.39 and Oranges = 2944.4) and I highlighted the amounts that sum to that customer's specific amount. Does anyone have experience with the "Optimizer" tool to help achieve this and to scale it for when the file has many customer transactions?

Test.xlsx

Datasets

Accepted answers

Qiu

@RCern
Your question reminds the Bin packing problem and maybe we can try that approach.

First I would filter out the record having "Amount in Bank" larger than "Target amount", then reuse the discussion here with an iterative macro.

I am not sure it this works for your larger dataset but at least it works for your sample dataset.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/HELP-How-to-use-ITERATIVE-MACRO/m-p/923606#M225693

0116-RCern.yxzp

All comments

philipmannering

Think that I have accomplished this. Be mindful that this may fail with a larger dataset, in which case there's nothing, that I'm aware of, that will fix it.

Sum to Target.yxzp

KGT

I realise that I don't have the solution below, but hopefully points in the right direction.

Also, this is the matchematical problem you're trying to solve: https://en.wikipedia.org/wiki/Subset_sum_problem

There are a couple of things to consider here.

What is the total size of your dataset and how many transactions per customer (roughly)
- That will define whether brute force is an option.
Will you always have exact matches to the cent?
- Is the total you are matching, an actual total of transactions, or are you trying to get close?
- This is important for the method chosen.

By the optimizer tool, does that mean the Optimization tool? If so, that is a predictive tool and may need hard boundaries to get exact matches. It's designed to optimise a process such as timesheeting, call centre response times, stock inventory etc.

Take a look at the Knapsack problem, if you want to pursue that. It's based around optimising the space in a knapsack by filling it as much as possible. Hopefully using that principle you'll be able to get exact every time. There may be actual training on the academy for it as well, as I know I showed the knapsack problem at 2 Inspires in training for the optimisation tool.

For Brute force,

You could create a table of every combination of amounts per customer and compare. The number of different transactions in each combination could vary as there is no point adding together 8*$15000, if the max total you'll ever compare is $50k.
I would create all nC2, then the nC3, etc. Using append all is the easiest way (probably join as company would need to match). A batch macro would also help with this.
If you have over 100 transactions on the regular, then that option might not be ideal.
Also, it may be just a result of the data you created, but there's definitely a pattern for the amounts contributing to the total. That will make it a ton easier if you can remove most of the options before trying.

Other options may involve adding transactions in decreasing size until you get close but will be super iterative.

Qiu

@RCern
Your question reminds the Bin packing problem and maybe we can try that approach.

First I would filter out the record having "Amount in Bank" larger than "Target amount", then reuse the discussion here with an iterative macro.

0116-RCern.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703