Array or other method to find individual amounts which add up to a total amount

[alekos7]

Hello,

I have a large population (30k+) of amounts which contribute to the total amount in another table. I was able to pull the two data-sets together, so I can see in one table the whole amount and all of the individual amounts. The issue is that for a given unique identifier (e.g., 12345), not all individual amounts add up to that. However, I am trying to determine which individual amounts add up to that whole amount. In some cases it's pretty straight forward where one of the amounts equals the total amount and I can distinguish those with ease, same goes for the instances where all of the amounts add up to the total amount. I'm having trouble coming up with a solution/formula to identify the individual amounts which aggregate to the total amount based on the unique ID.

For example, for unique ID 12345 I have a total amount of 55 and 7 individual amounts corresponding to that unique ID, but only 3 of them actually make up the 55 total amount (29.50, 29,50 and -4). I need to identify those 3 and call them our in a separate columns in some way. The whole amount will always be the same for a given unique ID.

Unique ID	Total Amount	Individual Amount	Result Option 1	Result Option 2
12345	55	29.50	29.50	12345 is a sum of 29.50, 29.50, -4.00
12345	55	29.50	29.50	12345 is a sum of 29.50, 29.50, -4.00
12345	55	-73.00
12345	55	-4.00	-4.00	12345 is a sum of 29.50, 29.50, -4.00
12345	55	-450.40
12345	55	201.33

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 262px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

12345 is a sum of 29.50, 29.50, -4.00

<tbody>
</tbody>

Any thoughts would be greatly appreciated!

 

[Rijnsent]

Hi Alekos,
try any of these solutions: https://www.extendoffice.com/docume...all-combinations-that-equal-given-sum.html#a1
Cheers,
Koen