Finding which values from the same column added together give me a given number

I have an expense of say 1,500, and I have an invoice of 100,000 in total... none of the individual expenses of the invoice is 1500 so I have to find which of all those added together give me 1500... when they are few there is no problem you can search manually, when they are a little more I can do it in excel using solver but here I have more than 350 and I wanted to see if I can make a workflow in Alteryx to do it.

Does anyone know how?

Help

Accepted answers

BS_THE_ANALYST

@jmrossetti that's actually fairly tricky. There could be loads of computations that add together to reach your target.

Anyway, after a little digging, it's apparent you might need some type of algorithm:

You could use the Python tool alongside Chatbot GPT to provide the algorithm, i.e. Backtracking:

I fed only 30 numbers into this and produced those combinations. The thing is, once you have lots of numbers, the time it takes to compute all the different scenarios will grow significantly:

All comments

binu_acs

@jmrossetti would you be able to provide and input file and expected output?

Raj

please share sample input and required output

jmrossetti

State	QTY	Amt Due
NY	180	1,621.93
KY	73	1,582.59
TX	68	1,563.98
KS	63	1,544.52
OH	138	1,518.16
CA	42	1,494.20
FL	100	1,484.22
CA	66	1,476.69
GA	194	1,467.84
MD	47	1,459.47
VA	46	1,454.09
NJ	59	1,448.74
MI	65	1,402.25
IL	86	1,389.88
NY	64	1,358.03
IL	59	1,309.47
CA	50	1,305.72
OH	143	1,285.72
TX	46	1,284.34
TX	63	1,277.09
CT	39	1,271.63
NY	47	1,246.80
MI	42	1,243.83
IL	42	1,196.99
DC	81	1,191.96
FL	41	1,178.64
TX	54	1,137.78
IL	40	1,137.70
DC	30	1,124.48
FL	35	1,104.31
CA	37	1,091.62
MA	107	1,042.34
NV	26	1,041.50
NC	33	1,039.25
NY	42	1,038.78
NJ	37	1,005.30
AK	36	982.81
OR	38	963.46
CA	24	946.78
AZ	20	927.16
FL	37	918.00
OK	85	886.31
CA	31	882.91
AZ	27	874.82
LA	26	869.83
FL	66	867.71
CA	37	861.63
CA	52	850.64
CA	91	848.88
MI	10	848.00
NY	38	847.08
TX	98	827.43
CA	23	806.73
MO	26	798.42
MO	18	791.76
MI	15	787.60
CA	17	780.05
CA	19	777.96
NY	30	775.61
MD	22	771.10
VA	25	755.77
PA	39	750.71
NE	28	750.70
AZ	30	748.02
NV	19	738.93
CA	17	727.56
IA	22	705.92
IL	30	701.86
SC	16	696.10
NJ	25	687.06
AL	16	651.87
GA	51	651.38
TX	28	642.52
GA	43	641.40
FL	23	637.76
CA	17	635.36
CO	20	629.99
CA	31	612.97
TN	15	612.26
TX	13	612.26
AZ	22	603.37
OH	22	601.62
FL	81	594.68
CA	26	593.02
ME	22	592.72
NM	18	590.16
MD	25	584.55
OR	18	568.27
VA	15	568.17
CA	22	564.76
MI	16	560.60
FL	45	560.02
MN	19	552.22
PA	16	547.44
SC	44	544.79
CA	18	542.94
WA	18	540.47
NY	23	525.96
TN	17	525.68
TN	15	523.06
PA	12	520.59
FL	17	520.14
WI	20	517.20
NJ	19	516.12
WI	17	514.58
CA	19	511.19
NY	18	508.42
KS	24	506.20
FL	19	500.22
AR	21	493.74
UT	13	492.57
CA	59	492.47
TX	17	488.16
MN	18	486.64
PA	19	484.95
CA	15	480.50
GA	22	475.76
UT	57	475.50
AL	10	474.47
NJ	14	468.59
MD	16	468.00
DE	14	467.49
NJ	17	467.02
CA	12	461.41
CA	18	452.33
CA	42	452.32
TX	12	451.76
NY	14	445.39
NJ	16	440.49
VA	13	438.25
RI	14	435.83
CO	16	431.01
MI	29	421.59
OH	17	417.84
OH	27	404.63
GA	12	403.31
PA	16	396.51
CA	9	394.42
CT	14	393.12
NJ	14	393.12
CA	9	383.48
NY	14	382.10
MA	8	381.98
PA	15	381.32
WI	11	376.52
CT	12	373.75
MA	40	373.45
NC	13	372.09
CA	10	370.02
UT	14	370.00
WA	11	367.98
OH	14	367.22
FL	12	362.07
MD	12	359.27
PA	14	356.50
MA	31	354.52
VA	16	354.34
WV	18	352.14
NJ	13	349.27
VA	14	348.50
ON	35	346.60
VA	9	342.87
FL	13	341.76
CA	14	338.36
OH	17	338.30
PA	8	336.42
NY	14	336.08
CA	10	329.48
CT	12	327.84
MI	11	326.29
CA	37	324.64
MI	11	321.82
TN	11	320.73
NM	8	319.99
NC	8	318.01
CO	12	314.36
FL	12	310.36
SC	11	308.99
FL	27	307.90
CA	4	307.75
NY	12	307.33
CA	7	302.90
FL	14	300.93
CA	3	300.00
CA	6	299.36
CA	10	299.04
OH	12	297.82
TX	14	297.40
AL	8	296.68
WI	11	295.64
CO	7	295.38
NJ	14	294.49
PA	27	293.99
CA	8	293.38
MS	9	292.76
PA	8	283.70
AB	20	282.77
AL	10	280.56
MA	9	278.40
MO	10	277.95
FL	13	275.44
FL	9	274.76
NC	11	272.65
IL	12	268.72
MD	7	267.86
MA	28	262.18
WI	13	261.82
IL	7	261.65
OH	9	255.00
FL	10	252.37
DE	10	249.96
SD	7	240.51
NJ	10	239.36
MI	6	238.15
AL	7	237.46
GA	15	232.20
AZ	5	230.26
MD	9	227.62
NY	11	227.47
MI	6	226.15
FL	7	225.02
CO	6	222.61
NH	7	221.63
NC	9	220.28
MO	8	218.72
OH	9	218.55
PA	8	218.32
VA	11	215.85
NY	9	215.31
FL	4	214.00
FL	3	213.00
CA	8	212.67
CA	7	211.22
CA	6	210.98
OH	7	208.55
FL	9	206.79
NJ	9	205.02
PA	16	204.28
IL	2	200.00
TX	8	196.24
CA	5	196.00
FL	22	193.44
FL	5	190.36
CT	7	188.48
NJ	7	183.05
NY	7	176.71
WA	5	172.04
MN	3	168.52
MN	3	166.67
PA	4	166.36
CT	8	165.93
CA	3	164.52
AL	5	162.14
FL	7	160.75
MA	3	156.33
ND	4	151.30
IL	4	150.26
CA	5	149.54
AR	6	143.19
OR	5	142.78
BC	12	142.20
AL	5	140.80
MI	6	140.33
KY	6	132.49
NH	6	131.13
FL	9	129.32
NC	4	127.69
FL	6	123.57
NY	9	121.22
TX	6	120.15
PA	6	117.76
CT	13	116.91
NY	4	115.01
AL	4	113.88
IL	4	112.62
WA	4	111.66
ME	5	109.16
MI	5	109.03
WA	4	108.46
TX	2	108.25
MN	2	108.13
IN	4	106.36
DC	2	106.00
MD	2	106.00
PA	2	106.00
PA	4	105.08
TX	4	104.95
IN	3	102.85
OH	4	101.83
CA	1	100.00
CA	1	100.00
CA	1	100.00
GA	5	96.98
CA	5	90.59
MD	3	89.62
TX	4	87.89
NY	3	87.10
NJ	3	85.30
CO	2	84.87
PA	4	84.80
PA	4	84.80
AL	4	80.40
NJ	2	80.00
CA	1	78.00
CA	1	78.00
CA	1	78.00
CA	3	76.26
OH	4	73.70
ID	10	73.17
VT	3	70.66
FL	3	67.36
WA	4	67.15
NY	4	64.80
TX	2	63.36
RI	9	63.18
VA	3	62.33
TX	3	61.61
IN	2	59.63
NY	6	52.66
TN	3	49.16
WA	2	44.12
OR	1	44.00
OR	2	43.96
FL	2	43.75
TN	2	43.70
NY	2	43.55
NY	2	43.25
NY	2	43.20
FL	2	43.00
NC	2	43.00
NC	2	42.90
IA	2	42.80
NC	2	42.80
NJ	2	42.65
NJ	2	42.65
NJ	2	42.65
VA	2	42.40
CA	2	40.68
CA	1	40.00
CA	1	40.00
CA	1	40.00
IL	1	40.00
PA	1	40.00
WY	5	39.90
TN	3	38.91
CT	2	31.91
NJ	1	28.08
GA	3	26.73
FL	1	22.62
LA	2	20.68
DE	2	13.32

I need to find wich of all the amounts listed added together result 1742.26

BS_THE_ANALYST

You could use the Python tool alongside Chatbot GPT to provide the algorithm, i.e. Backtracking:

I fed only 30 numbers into this and produced those combinations. The thing is, once you have lots of numbers, the time it takes to compute all the different scenarios will grow significantly:

jmrossetti

Thank you for taking the time!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703