A couple of other options to consider. I have done these on a single sheet for simplicity here but either method could be adapted to put the results on a second sheet.
Method 1
If you have the column headings (row 9 below) available then you can achieve the results with worksheet formulas.
B10 is copied down.
B11 is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.
Arrange
 A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X 
1  ARK109  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
2  ARK110  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
3  ARK111AREA1  1 x BYODV  1 x VIS  1 x VRRM  1 x WCH  1 x PC  1 x DP  1 x VRARM  1 x VRFM  1 x LEC  1 x LCF  1 x SYM  1 x BYODH  1 x BYODW  1 x DVD  1 x PRJ  1 x SPKR        
4  ARK111AREA2  1 x WCH                       
5  ARK112  1 x BYODC  1 x VIS  1 x NET  1 x WCH  1 x BYODW  1 x PC  1 x DP  1 x DVD  1 x LEC  1 x SPKR              
6  ARK115  1 x BYODV  1 x VIS  1 x NET  1 x VRRM  1 x WCH  1 x PC  1 x DP  1 x VRARM  1 x VRFM  1 x DVD  1 x FIXT  1 x BLA  1 x IL  1 x LCF  1 x LEC  1 x SYM        
7                         
8                         
9   BYODC  LFD  VIS  WCH  PC  WGB  DVD  LEC  SYM  BYODV  VRRM  DP  VRARM  VRFM  LCF  BYODH  BYODW  PRJ  SPKR  NET  FIXT  BLA  IL 
10  ARK109  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
11  ARK110  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
12  ARK111AREA1    1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC  1 x SYM  1 x BYODV  1 x VRRM  1 x DP  1 x VRARM  1 x VRFM  1 x LCF  1 x BYODH  1 x BYODW  1 x PRJ  1 x SPKR     
13  ARK111AREA2     1 x WCH                    
14  ARK112  1 x BYODC   1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC     1 x DP      1 x BYODW   1 x SPKR  1 x NET    
15  ARK115    1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC  1 x SYM  1 x BYODV  1 x VRRM  1 x DP  1 x VRARM  1 x VRFM  1 x LCF      1 x NET  1 x FIXT  1 x BLA  1 x IL 
Spreadsheet Formulas 
Cell  Formula  A10  =A1  B10  {=CONCAT(IF(RIGHT($B1:$Q1,LEN(B$9)+2) = "x "&B$9,$B1:$Q1,""))} 

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER! 
Excel tables to the web >> Excel Jeanie HTML 4
Method 2
You could try this macro. Results of the macro are in rows 18:24 below.
Code:
Sub ArrangeInColumns()
Dim d As Object
Dim a As Variant, b As Variant
Dim r As Long, c As Long, uba2 As Long
Dim s As String
a = Range("A1").CurrentRegion.Value
uba2 = UBound(a, 2)
Set d = CreateObject("Scripting.Dictionary")
d(1) = Empty
ReDim b(1 To UBound(a, 1) + 1, 1 To uba2)
For r = 1 To UBound(a)
b(r + 1, 1) = a(r, 1)
For c = 2 To uba2
If IsEmpty(a(r, c)) Then Exit For
s = Split(a(r, c), " x ")(1)
If Not d.exists(s) Then
d(s) = d.Count + 1
If d.Count > UBound(b, 2) Then ReDim Preserve b(1 To UBound(b), 1 To UBound(b, 2) + 1)
b(1, d(s)) = s
End If
b(r + 1, d(s)) = a(r, c)
Next c
Next r
Range("A18").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
Arrange
 A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  X 
1  ARK109  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
2  ARK110  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
3  ARK111AREA1  1 x BYODV  1 x VIS  1 x VRRM  1 x WCH  1 x PC  1 x DP  1 x VRARM  1 x VRFM  1 x LEC  1 x LCF  1 x SYM  1 x BYODH  1 x BYODW  1 x DVD  1 x PRJ  1 x SPKR        
4  ARK111AREA2  1 x WCH                       
5  ARK112  1 x BYODC  1 x VIS  1 x NET  1 x WCH  1 x BYODW  1 x PC  1 x DP  1 x DVD  1 x LEC  1 x SPKR              
6  ARK115  1 x BYODV  1 x VIS  1 x NET  1 x VRRM  1 x WCH  1 x PC  1 x DP  1 x VRARM  1 x VRFM  1 x DVD  1 x FIXT  1 x BLA  1 x IL  1 x LCF  1 x LEC  1 x SYM        
7                         
17                         
18   BYODC  LFD  VIS  WCH  PC  WGB  DVD  LEC  SYM  BYODV  VRRM  DP  VRARM  VRFM  LCF  BYODH  BYODW  PRJ  SPKR  NET  FIXT  BLA  IL 
19  ARK109  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
20  ARK110  1 x BYODC  1 x LFD  1 x VIS  1 x WCH  1 x PC  2 x WGB  1 x DVD  1 x LEC  1 x SYM               
21  ARK111AREA1    1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC  1 x SYM  1 x BYODV  1 x VRRM  1 x DP  1 x VRARM  1 x VRFM  1 x LCF  1 x BYODH  1 x BYODW  1 x PRJ  1 x SPKR     
22  ARK111AREA2     1 x WCH                    
23  ARK112  1 x BYODC   1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC     1 x DP      1 x BYODW   1 x SPKR  1 x NET    
24  ARK115    1 x VIS  1 x WCH  1 x PC   1 x DVD  1 x LEC  1 x SYM  1 x BYODV  1 x VRRM  1 x DP  1 x VRARM  1 x VRFM  1 x LCF      1 x NET  1 x FIXT  1 x BLA  1 x IL 
Excel tables to the web >> Excel Jeanie HTML 4
Like this thread? Share it with others