Finding combinations of numbers adding to a given value

Hi All,

Is there any function in Alteryx that can find combinations of numbers adding to a given value? I have roughly 400-500 values including both positive and negative values and I want to see what combination equals to the sum of these values.

I attach the sample data here, the value highlighted in yellow is the correct combination. And how can I find the combination using Alteryx?

Thanks in advance!

Alteryx Community Data.xlsx

Workflow

Developer

Common Use Cases

Help

Optimization

Accepted answers

philipmannering

Hi @JeremyZ, if you have the R Tools installed you can use the Optimization Tool to get your answer. I have found that 197 of your 488 values can be summed to get the answer. However, this is quite possibly not the only solution. Workfow attached,

Sum to Target.yxmd

All comments

Qiu

@JeremyZ
Is this combination to be consecutive?

JeremyZ

Hi Qiu,

it is not consecutive.

Yoshiro_Fujimori

@JeremyZ ,

I think it is not practical to find a combination of numbers which sums up to a given number,

because there are huge number of combinations to check.

nCr (n=500, r=10) = 2.458105888 E+20...

JeremyZ

@Yoshiro_Fujimori If the data volume is smaller (e.g. 100-200), is it possible to do that?

philipmannering

Sum to Target.yxmd

Yoshiro_Fujimori

Hi @JeremyZ ,

If we keep using the brute force approach, I am afraid the number of possible combinations to check is still big.

nCr (n = 100, r = 10) = 1.7e+13 (=17 Tera)

The solution with Optimization Tool by @PhilipMannering is quite impressive, as it actually got to one of the correct answers!

However, I am not sure how to make it to get your expected answer (a set of 11 numbers) with this approach...

JeremyZ

@PhilipMannering Thank you! It is quite impressive.

BTW, do you know how to make the solution size smaller if possible? e.g. the set of 11 numbers in my excel file.

Thanks in advance.

philipmannering

Hi Jeremy,

Unfortunately not, and not for lack of trying. I can get your solution if I sample to the first 36 records, but unfortunately I get a duff answer beyond that. I feel like my solution should work and yet it doesn't. Have a play and let me know if you can get it working.

Sum to Target 2.yxmd

Starship5000

Came across this thread and had a question, is there a way to add a constraint on the count of items returned? For example, identify a combination of numbers that sums to 100 and the combination has to be fewer than 20 numbers long?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703