I need help with running the formula I currently have but only when the locations are the same on the two tables.
Table 1
Table 2
This is the formula I am using ( below ) to calculate the difference between the two but I am having trouble creating a formula that will pull the data from both tables when the Location is the same and they are not in the same order. As we can see table one has DDD but table two does not.
Table 1
| Location | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9 |
| AAA | 1% | 0% | 0% | 0% | 0% | 0% | 0% | 98% | 0% |
| BBB | 23% | 0% | 0% | 17% | 0% | 52% | 0% | 8% | 0% |
| CCC | 100% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% |
| DDD | 0% | 100% | 0% | 0% | 0% | 0% | 0% | 0% | 0% |
| EEE | 20% | 3% | 2% | 10% | 1% | 1% | 0% | 10% | 53% |
| FFF | 0% | 0% | 0% | 99% | 0% | 0% | 0% | 1% | 0% |
| GGG | 0% | 0% | 0% | 100% | 0% | 0% | 0% | 0% | 0% |
Table 2
| Location | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9 |
| AAA | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 100% | 0% |
| BBB | 54% | 0% | 0% | 15% | 0% | 24% | 0% | 6% | 0% |
| CCC | 100% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% |
| EEE | 16% | 3% | 1% | 5% | 1% | 1% | 0% | 5% | 68% |
| FFF | 0% | 0% | 0% | 100% | 0% | 0% | 0% | 0% | 0% |
| GGG | 0% | 0% | 0% | 100% | 0% | 0% | 0% | 0% | 0% |
This is the formula I am using ( below ) to calculate the difference between the two but I am having trouble creating a formula that will pull the data from both tables when the Location is the same and they are not in the same order. As we can see table one has DDD but table two does not.
| Location | C1 | C2 | C3 | C4 | C5 | C6 | C7 | C8 | C9 |
| AAA | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 | =+(N2-B2)*100 |
| BBB | |||||||||
| CCC | |||||||||
| DDD | |||||||||
| EEE | |||||||||
| FFF | |||||||||
| GGG |
