Hi all,
Am trying to do something fairly straightforward, but trying to wrap my head around how....
I have 1 input table, and 2 reference tables. All tables are from different sheets in the same Excel workbook
Input Table
| Company | Sales | Revenue |
| A | 120 | 200 |
| B | 240 | 600 |
| C | 100 | 200 |
Reference Table 1
| Company | Location | Type | Sales | Revenue |
| A | US | Fixed | 20% | 35% |
| A | US | Variable | 30% | 15% |
| A | Europe | Fixed | 25% | 40% |
| A | Asia | Variable | 25% | 10% |
| B | Europe | Mixed | 45% | 35% |
| B | Europe | Variable | 25% | 15% |
| B | Asia | Fixed | 30% | 50% |
Reference Table 2
| Company | Location | Type | Sales | Revenue |
| Default | US | Fixed | 20% | 35% |
| Default | Europe | Variable | 80% | 65% |
What I need to do is search the Company from the Input table against Reference Table 1 and split the Sales and Revenue absolute values in the input table by the percentages in Reference Table 1.
If a company is found in the input table which is not in the reference table, split the sales and revenue values based on Reference Table 2 (the default).
The Output table should look like this:
| Company | Location | Type | Sales | Revenue |
| A | US | Fixed | 24 | 70 |
| A | US | Variable | 36 | 30 |
| A | Europe | Fixed | 30 | 80 |
| A | Asia | Variable | 30 | 20 |
| B | Europe | Mixed | 108 | 210 |
| B | Europe | Variable | 60 | 90 |
| B | Asia | Fixed | 72 | 300 |
| C | US | Fixed | 20 | 70 |
| C | Europe | Variable | 80 | 130 |
Any ideas about the cleanest most elegant way to do this?
