Concatenate with obstacles

olfleck · Mar 8, 2017

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

Rick Rothstein · Mar 9, 2017

Give this macro a try...

Code:

[table="width: 500"]
[tr]
	[td]Sub RearrangeData()
  Dim X As Long, R As Long, Rng As Range, Ar As Variant, Joined As String, Result As Variant
  Set Rng = Intersect(Columns("A").SpecialCells(xlConstants).EntireRow, Columns("A:D"))
  Const FirstOutputCell As String = "H1"
  ReDim Result(1 To Rng.Areas.Count, 1 To 2)
  For X = 1 To Rng.Areas.Count
    Ar = Rng.Areas(X)
    Result(X, 1) = Ar(2, 1)
    For R = 2 To UBound(Ar)
      Joined = Replace(Ar(R, 3) & Ar(R, 4), "-", "")
      If Len(Joined) Then Result(X, 2) = Result(X, 2) & ", " & Ar(R, 2) & " (" & Format(100 * Joined, "General Number") & "%)"
    Next
    Result(X, 2) = Mid(Result(X, 2), 3)
  Next
  Range(FirstOutputCell).Resize(, 2) = Rng(1).Resize(, 2).Value
  Range(FirstOutputCell).Offset(1).Resize(UBound(Result), 2) = Result
  Range(FirstOutputCell).Resize(, 2).EntireColumn.AutoFit
End Sub[/td]
[/tr]
[/table]

olfleck · Mar 9, 2017

Thanks for the quick answer! It gives me a type mismatch error. Tried defining everything as Variant but didn't help..

Rick Rothstein · Mar 9, 2017

olfleck said:
Thanks for the quick answer! It gives me a type mismatch error. Tried defining everything as Variant but didn't help..

Just telling us there is an error does not help. Run my code again and when the error message appears, click the Debug button and tell us what line of code is highlighted in yellow.

Edit Note 1: Also, tell us what columns your data are in.

Edit Note 2: When you said in your original post that your input data was "Table 1"... did you mean a real, Excel table object (with the name "Table 1") or just a range of cells that you think of as being a table.

Aladin Akyurek · Mar 9, 2017

If you are on an Excel version which has the TEXTJOIN version...

In G1 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=$F1,IF(ISNUMBER($C$2:$C$20)," "&$B$2:$B$20&" ("&$C$2:$C$20&")",IF(ISNUMBER($D$2:$D$20)," "&$B$2:$B$20&" ("&$D$2:$D$20&")","")),""))

where F1 = A, etc.

olfleck · Mar 9, 2017

Aladin Akyurek said:
If you are on an Excel version which has the TEXTJOIN version...

In G1 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF($A$2:$A$20=$F1,IF(ISNUMBER($C$2:$C$20)," "&$B$2:$B$20&" ("&$C$2:$C$20&")",IF(ISNUMBER($D$2:$D$20)," "&$B$2:$B$20&" ("&$D$2:$D$20&")","")),""))

where F1 = A, etc.

I only have Excel 2013 should have mentioned that... Don't think it works in there. Thank you anyway!!

olfleck · Mar 9, 2017

Rick Rothstein said:
Just telling us there is an error does not help. Run my code again and when the error message appears, click the Debug button and tell us what line of code is highlighted in yellow.

Edit Note 1: Also, tell us what columns your data are in.

Edit Note 2: When you said in your original post that your input data was "Table 1"... did you mean a real, Excel table object (with the name "Table 1") or just a range of cells that you think of as being a table.

I just meant the table that I posted. There table is not called Table 1. The following is highlighted:

Result(X, 2) = Result(X, 2) & ", " & Ar(R, 2) & " (" & Format(100 * Joined, "General Number") & "%)"

The segments are in E and the numbers in F (for 2016) or E (for 2015).

olfleck · Mar 9, 2017

olfleck said:
I just meant the table that I posted. There table is not called Table 1. The following is highlighted:

Result(X, 2) = Result(X, 2) & ", " & Ar(R, 2) & " (" & Format(100 * Joined, "General Number") & "%)"

The segments are in E and the numbers in F (for 2016) or E (for 2015).

But I tried it in my sample sheet which looks exactly like the one I posted. With the first column being column A.

Aladin Akyurek · Mar 9, 2017

olfleck said:
I only have Excel 2013 should have mentioned that... Don't think it works in there. Thank you anyway!!

Ok. A macro as Rick suggests is a good idea. If you prefer a formula, we can use a work-alike for TEXTJOIN...

Rick Rothstein · Mar 9, 2017

olfleck said:
The segments are in E and the numbers in F (for 2016) or E (for 2015).

I assume the "segments" are what you labeled in your table as "Geographic Segments"... if those are in Column E, then how can the 2015 numbers also be in Column E? In addition, in order to write code to address your data, we need to know where are the parts are (can't pull data from them if we don't know where they are). Tell us the column letters for each of these columns...

Company Name:

Geographic Segments:

2016:

2015:

EDIT NOTE: Your response in Message #8 makes it sound like you simplified your layout for this question and that your actual data may not be contiguous like your sample shows. If that is the case, please tell us where your data really is (we code to what you tell us... most times the solution to the simplified layout does not easily scale to a more disjointed layout).

Concatenate with obstacles

olfleck

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

olfleck

New Member

Rick Rothstein

MrExcel MVP

Aladin Akyurek

MrExcel MVP

olfleck

New Member

olfleck

New Member

olfleck

New Member

Aladin Akyurek

MrExcel MVP

Rick Rothstein

MrExcel MVP

Similar threads

Forum statistics

Share this page

Concatenate with obstacles

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

MrExcel MVP

New Member

New Member

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock