JumboCactuar
Well-known Member
- Joined
- Nov 16, 2016
- Messages
- 785
- Office Version
- 365
- Platform
- Windows
Hi,
I have 2 tables
Table1 - Source with Total Points
<tbody>
</tbody>
and Table 2 is input data:
<tbody>
</tbody>
I need to get a breakdown of each day points, but the requirement is that the first ID of the user must be fulfilled and if exceeded then rest are assigned to their next ID until filled:
So expected result is like so:
<tbody>
</tbody>
So Luke for example:
6 Points on Day 2
7 Points on Day 3
but Luke: ID 2 only holds 5 Points
5 Points goes to ID 2
Next points go to their next ID
Sometimes more than 2 IDs
Any help appreciated
I have 2 tables
Table1 - Source with Total Points
ID | Name | Points |
1 | John | 23 |
2 | Luke | 5 |
4 | Tim | 11 |
5 | Fran | 1 |
6 | Bob | 6 |
13 | Bob | 6 |
14 | Luke | 8 |
15 | Tim | 3 |
<tbody>
</tbody>
and Table 2 is input data:
Name | Points | Day |
John | 5 | 1 |
John | 1 | 1 |
John | 17 | 2 |
Luke | 6 | 2 |
Luke | 7 | 3 |
Tim | 14 | 3 |
Fran | 1 | 3 |
Bob | 4 | 3 |
Bob | 8 | 4 |
<tbody>
</tbody>
I need to get a breakdown of each day points, but the requirement is that the first ID of the user must be fulfilled and if exceeded then rest are assigned to their next ID until filled:
So expected result is like so:
Day | ID | Name | Points |
1 | 1 | John | 6 |
2 | 1 | John | 17 |
2 | 2 | Luke | 5 |
2 | 14 | Luke | 1 |
3 | 14 | Luke | 7 |
3 | 4 | Tim | 11 |
3 | 15 | Tim | 3 |
3 | 5 | Fran | 1 |
3 | 6 | Bob | 4 |
4 | 6 | Bob | 2 |
4 | 13 | Bob | 6 |
<tbody>
</tbody>
So Luke for example:
6 Points on Day 2
7 Points on Day 3
but Luke: ID 2 only holds 5 Points
5 Points goes to ID 2
Next points go to their next ID
Sometimes more than 2 IDs
Any help appreciated
Last edited: