Grouping numbers by a list of given value

Question

Hi,

Is it possible to group a list of numbers by specific given value?

For each account (AAA), there will be 2 or more numbers as grouping reference (600,900), the alteryx tool need to decide from input 1 that how should (100,200,300,400,500) be grouped to 600 and 900.

For example, give 600 and 900, 100,200,300,400,500 would be grouped to

100+200+300 =600

400+500 =900

Detailed data, number, output as attached:

Input1: Data to be grouped

Account Value DateAmountAAA12/1/2023100AAA12/1/2023200AAA12/1/2023300AAA12/1/2023400AAA12/1/2023500BBB12/2/2023100BBB12/2/2023200BBB12/2/2023300BBB12/2/2023400BBB12/2/2023500BBB12/2/2023250BBB12/2/2023210BBB12/2/2023110BBB12/2/2023190

Input2: grouping reference

Account Value DateGoalAAA12/1/2023600AAA12/1/2023900BBB12/2/20231750BBB12/2/2023510

Output:

Account Value DateAmountGoalAAA12/1/2023100600AAA12/1/2023200600AAA12/1/2023300600AAA12/1/2023400900AAA12/1/2023500900BBB12/2/20231001750BBB12/2/20232001750BBB12/2/20233001750BBB12/2/20234001750BBB12/2/20235001750BBB12/2/20232501750BBB12/2/2023210510BBB12/2/2023110510BBB12/2/2023190510

sample data.xlsx

apathetichell · Answer

is this a number theory experiment? What's the context here? As @CoG succinctly pointed out - and I ambigously referenced - your rules do not work because there are multiple potential combinations.  You can try to experiment using the optimizatoin tool for some of this stuff. But I'm not really sure what you're looking to do based upon your descriptoin.

CoG · Answer

@Paddi

The difficulty with this problem is 2 fold, there may be many occasions where multiple solutions exist. How do you handle this? For example, with the input provided:

100+500 = 600

200+300+400 = 900

-OR-

100+200+300 = 600

400+500 = 900

How do you know which to assign? The second issue is more pragmatic. What you are looking for is a solution to a partition problem, which involves permutations, of which there can be many possibilities. Just for the input you provided, there are 540 different ways to group amounts together. Solving this problem can get very big very quickly, which can be slow to run or crash completely.

That being said, if precise grouping doesn't matter, then here is a workflow that does what you're looking for:

There is a macro included (not pictured here for brevity's sake) that computes all possible partitions.

Sample Workflow.yxzp

Paddi · Answer

Hi @apathetichell ,

I have updated the logic in words, hope it helps. Kindly let me know if any questions.