Finding combinations of numbers adding to a given sum

Here is my dilemma. I have a large file of data...maybe a millions rows (I'm sure I can narrow this down). One of the fields is a value. I want to find combinations of records where the sum of the value equals a given number. I don't have an example because I don't even know where to begin.

For example, let's say I'm looking for $1,410.15 within the data set. That amount could be made up of any number of records that net to $1,410.15.

Has anyone built anything for this type of scenario in Alteryx? I've seen some stuff on the internet but it looks too advanced for me.

Preparation

Accepted answers

AmitMiller

could this help?

find combinations.yxmd

All comments

MsBindy

Good grief...that is not supposed to be the 'subject'....how do I fix that?....figured it out.

NicoleJohnson

Wow, this one feels like a super-challenge! If someone solves this, you would make my accounting department very VERY happy when it comes time to do reconciliations...

@MsBindycan you provide a couple clarifications?

1. Are the values only positive, or are there negative values as well? (Asking because if they're positive, then at least you can start out by removing any values greater than your final value)

2. Can it be any combination of values that reaches the desired amount? Or are you limited to 2 values, 5 values, etc.? (Asking because that might make it easier to resolve if you don't have to iterate for any infinite number of combinations).

MsBindy

haha....I am actually helping the accounting department!

1. Are the values only positive, or are there negative values as well? Positive and Negative

2. Can it be any combination of values that reaches the desired amount? Any combination of numbers

AmitMiller

could this help?

find combinations.yxmd

MsBindy

Very cool. It looks like it works! Thanks.

ian_hedrick

@AmitMiller

Hi, is there a way to modify this so that you are only given one combination and not every possible combination? Trying to help cut down on the time it takes for the workflow to run with 200+ records. Thanks in advance!

dsandmann

@ian_hedrick

You just need to make some minor changes to the code that generates combos of different lengths:

# Generate combinations of different lengths
for r in range(1, len(values) + 1):
    for combination in itertools.combinations(values, r):
        if sum(combination) == target_amount:
            valid_combinations.append(combination)
            break
    if valid_combinations:
        break

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703