I'm struggling to find a solution for a spreadsheet on google sheets using JavaScript. I've come across terms like group by and discard, but I can't seem to put together a mental image that would lead to any results.
Within a dataset, I need to identify individual items and groups of items (summed) that match a specified value.
In my data sheet, I have a list with defined values:
| item | value |
|---|---|
| 1 | 100.00 |
| 2 | 53.50 |
| 3 | 45.00 |
| 4 | 67.00 |
| 5 | 32.50 |
| 6 | 35.60 |
| 7 | 34.70 |
I am looking to create groups of items where their total sum ideally equals 100.00. However, a slightly higher sum could also be acceptable, but the ideal is up to (<=).
The expected result logic is as follows:
Group 1 = item 1 value 100.00
Group 2 = item 4 value 67.00 and item 5 value 32.50 (closest to 100 with a sum of 99.50)
Group 3 = item 2 value 53.50 and item 3 value 45.00 (closest to 100 with a sum of 98.50)
Group 4 = item 6 value 35.60 + item 7 value 34.70 (combined sum is 70.30)
The order of items or formation of item groups does not matter. Only unique items can be included in each group.
The output should display only the items from each group. For example:
1
4 5
2 3
6 7
An ideal output would include all results (group, items, total value):
Group 1 item(s) 1 total value 100.00
Group 2 item(s) 4 5 total value 99.50
Group 3 item(s) 2 3 total value 98.50
Group 4 item(s) 6 7 total value 70.30
I have a concept in mind, but I am still figuring out how to execute it.
| Action | Rule | Result |
|---|---|---|
| Determine the maximum value of an ITEM as the LIMITER must not be lower | Maximum value | Display the maximum value. Example: 100 |
| Set the LIMITER as long as it is equal to or less than an ITEM's maximum value | <= MaximumValue | Example: LIMITER: 100 |
| Check if any ITEM has a value equal to the LIMITER 100 | value == LIMIT | Assign a sequential group number to items with a value of 100 first |
| Check if the sum of any ITEMS equals the LIMITER 100 | sum of items = value of the limiter, without repeating items already listed | Assign a sequential group number to items summed to reach 100 |
| Check if any ITEM is = -0.01 which was searched earlier | without repeating items already listed | Assign a sequential group number to items with values below the limiter |
| Check if the sum of any items is = -0.01 which was searched earlier | sum of items = value of fetched, without repeating items already listed | Assign a sequential group number to items summed with values below the limiter |
| Continue searches until no more values remain | without repeating items already listed | Finalize the list with the last available item |
Any assistance would be greatly appreciated.