Finding combinations of numbers adding to a given value

[JeremyZ]

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! :)

[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 · Accepted solution]

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,

[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.

[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?