Finding a combination (just one is enough) of numbers adding up to a given value

[Nathashawije]

Hi all,
Currently I am working on a project and it has a part that I need to figure out what are the numbers adds up to a certain value. This is doable in Excel, but right now I am automating the process in Alteryx. I have read and tried so many ways but couldn't figure out a best way to do this. 

I have attached my data sheet.
'Data' tab- This has all data. there are 32 items, each item has a unique code which is a combination of ID+ID_code, each code combination has different prices
'Totals' tab - I want to get what values in each item adds up to the total amount in this tab

Let me know if you have any thoughts/ ideas to make this work.
Thanks in advance. 

[gawa]

Hi @Nathashawije 

You want to sum up all value of each item1 to 32? If so, first transpose data into vertical shape, and summarize values, and crosstab to the desired format. Please refer to attached workflow. 

[Nathashawije]

Thanks for your work @gawa. I am sorry if my question was not clear enough. 

I want to find what values under item1 adds upto a total value in 'Totals' tab in my attached data sheet. 

Ex: Under Item1 there are 3250 values (172.63, 128.27, 804.70, 258.18 and so on). I want to know which sum(value combination) is equal to 5006.89. 

[Qiu]

@Nathashawije 
We might be able to get an approximate solution with Bin Packing approach.
But can you check your Total again?

Ex. For item1, you want to pick the values that can be summed up to " -8092.87", but I checked the Excel, I only get up to "-7733" for negative value.

[Nathashawije]

@Qiu I am so sorry I have accidently copied the wrong Total row. I have corrected the excel sheet. 

I'll take a look at Bin Packing approach, thanks for suggesting.

[Qiu]

@Nathashawije 
Thank you for checking it out for me.
@SPetrie provided a solution for the Bin Packing issue here and I usually use his solution.
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/HELP-How-to-use-ITERATIVE-MACRO/m-p/92...

I tested with "Item 1" and I am sure we can use another batch macro to run thought all other items in your input.

Batch Macro should be updateing the Total valve highlighted in the snap of macro in below.

Hope it can be a bit help.