I have two datasets.
(a) table
and
(b) table
| Group | Factor |
| A | 4 |
| A | 12 |
| A | 100 |
| A | 4200 |
| A | 5100 |
| A | 6200 |
| B | 1 |
| B | 4 |
| B | 13 |
| B | 190 |
| B | 30000 |
| B | 889199995 |
What I need to do is
For each group in the table (a), I have to multiply with the numbers from the top in the same group in the table (b)
Please see below
| Group | Factor | | |
| A | 4 | 4*2=8 | => it becomes the new number of Group A in the table (a) |
| A | 12 | 8*12=96 | => the new number 8 from the previous calculation is compared with 12. If this new number is bigger than 12, then it stops. However if the number is smaller (which is the case in this situation), the new number 8 is being multiplied by 12. Then, this 96 becomes a new number of the group A in the table a |
| A | 100 | 96*100 = 9600 | => Now 96 is compared with 100 and since it is smaller, I multiply 96 by 100 |
| A | 4200 | | => Now 9600 is bigger than 4200. It stops here and 9600 becomes the final number of the group A in the table a |
| A | 5100 | | |
| A | 6200 | | |
| B | 1 | | The same process starts with 3 since this is the B group number in the table a. This process will stop after multiplying 3000 and the final B group number in the table a will be 889200000 |
| B | 4 | | |
| B | 13 | | |
| B | 190 | | |
| B | 30000 | | |
| B | 889199995 | | |
Not sure if I'm explaining the logic well. It seems pretty complicated case for me and not sure whether I should use macros or multi-row formula to solve this problem.
In real situation, I would have thousand of numbers in the table a...
Any advice and solution will be greatly appreciated!!!
