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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Total Amount[/TD]
[TD]Individual Amount[/TD]
[TD]Result Option 1[/TD]
[TD]Result Option 2[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]29.50[/TD]
[TD]29.50[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]29.50[/TD]
[TD]29.50[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-73.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-4.00[/TD]
[TD]-4.00[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-450.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]201.33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 262px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
</tbody>[/TABLE]
Any thoughts would be greatly appreciated!
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique ID[/TD]
[TD]Total Amount[/TD]
[TD]Individual Amount[/TD]
[TD]Result Option 1[/TD]
[TD]Result Option 2[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]29.50[/TD]
[TD]29.50[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]29.50[/TD]
[TD]29.50[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-73.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-4.00[/TD]
[TD]-4.00[/TD]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]-450.40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]55[/TD]
[TD]201.33[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
****** id="cke_pastebin" style="position: absolute; top: 262px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]12345 is a sum of 29.50, 29.50, -4.00[/TD]
[/TR]
</tbody>[/TABLE]
Any thoughts would be greatly appreciated!
