Excel: Concatenate with obstacles


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)

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%
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%)


This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of March 08, 2017.


For more resources for Microsoft Excel