Hi,
I have the following excerpt from a system (Table 1). However I need to change this input and make it look like the Table 2.
The data is not very homogenous, which makes it very troublesome to use the CONCATENATE or any other Function. Sometimes the system can only find old data (see company B) so then I have to work with this. Sometimes it puts Geographic Segments and says they are 0(-) as you can see with company A below, which should not be in the output.
It usually is more than 3 companies and each of them can have various numbers of segments they operate in.
The output for each company should look like the following:
Segment 1 (Percentage), Segment 2 (Percentage),...
Can anyone help me to write a macro that solves this problem?
Let me know if something is not clear and thank you in advance!
Table 1 (INPUT)
<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
OUTPUT:
<colgroup><col><col></colgroup><tbody>
</tbody>
I have the following excerpt from a system (Table 1). However I need to change this input and make it look like the Table 2.
The data is not very homogenous, which makes it very troublesome to use the CONCATENATE or any other Function. Sometimes the system can only find old data (see company B) so then I have to work with this. Sometimes it puts Geographic Segments and says they are 0(-) as you can see with company A below, which should not be in the output.
It usually is more than 3 companies and each of them can have various numbers of segments they operate in.
The output for each company should look like the following:
Segment 1 (Percentage), Segment 2 (Percentage),...
Can anyone help me to write a macro that solves this problem?
Let me know if something is not clear and thank you in advance!
Table 1 (INPUT)
Company Name | Geographic Segments | 2016 | 2015 | |
1 | A | United States | 54.5% | - |
2 | A | Canada | 6.8% | - |
3 | A | China | 4.9% | - |
4 | A | Germany | 4.4% | - |
5 | A | All Other | 29.4% | - |
6 | A | United Kingdom | - | - |
7 | A | Netherlands | - | - |
Company Name | Geographic Segments | 2016 | 2015 | |
1 | B | East China | - | 12.4% |
2 | B | Southern China | - | 78.0% |
3 | B | Central China | - | 3.9% |
Company Name | Geographic Segments | 2016 | 2015 | |
1 | C | Canada | - | 3.4% |
2 | C | United Kingdom | - | 2.1% |
3 | C | Switzerland | - | - |
4 | C | Brazil | - | 1.0% |
5 | C | Mexico | - | 0.9% |
<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
OUTPUT:
Company Name | Geographic Segments |
A | United States (54.5%), All Other (29.4%), Canada (6.8%), China (4.9%), Germany (4.4%) |
B | Southern China (78%), East China (12.4%), Central China (3.9%) |
C | Canada (3.4%), United Kingdom (2.1%), Brazil (1%), Mexico (0.9%) |
<colgroup><col><col></colgroup><tbody>
</tbody>