Dear all,
I would like to turn Table A into Table B with below logic.
Table A:
| Product | Duration | Due Fail | 1-11 | 12-22 | 23-33 |
|---|
| A | 10 | 5 | 2 | 7 | 0 |
| B | 20 | 4 | 0 | 1 | 3 |
| C | 10 | 8 | 4 | 6 | 0 |
| D | 20 | 10 | 6 | 2 | 1 |
Table B:
| Product | Duration | Due Fail | 1-11 | 12-22 | 23-33 |
|---|
| A | 10 | 7 (5+2) | 7 | 0 | 0 |
| B | 20 | 5 (4+0+1) | 3 | 0 | 0 |
| C | 10 | 12 (8+4) | 6 | 0 | 0 |
| D | 20 | 18 (10+6+2) | 1 | 0 | 0 |
For example,
Product A with Duration 10 in Table A,
The lower limit in column 1- 11 is 1, after minus 10 is less than 1,
then it fall into the column "Due Fail" and sum with the value in "Due Fail" ,
else
if the lower limit in column 12- 22 is 12, after minus 10 is equal to 2, it will fall into range "1-11",
then it fall into the column "1-11" but do not need to sum the original value in "1-11"
2 in "1-11" in Table A will fall into the column "Due Fail" and sum with the original value 5
7 in "12-22" in Table A will fall into the column "1-11" .
Product B with Duration 20
if the lower limit in column 1- 11 is 1, after minus 20 is less than 1,
then it fall into the column "Due Fail" and sum with the value in "Due Fail" ,
else
if the lower limit in column 12- 22 is 12, after minus 20 is less than 1,
then it fall into the column "Due Fail" and sum with the value in "Due Fail" ,
else
if the lower limit in column 23- 33 is 23, after minus 20 is 3, it will fall into the range "1-11",
then it fall into the column "1-11" but do not need to sum the original value in "1-11"
0 in "1-11" in Table A will fall into the column "Due Fail" and sum with the original value 4
1 in "12-22" in Table A will fall into the column "Due Fail" and sum with the original value 4
3 in "23-33" in Table A will fall into the column "1-11"
Is it possible to do it?
Please let me know if further explanation is needed.
In my real case, there is 36 column in range that means the last column is "366+"
Thanks a lot.
Best Regards,
Louis
