Sorting data into headed columns

V

VEESORS_2

New Member
Joined
Mar 12, 2019
Messages
3
Hi -
I've exported data from another program and done text to columns, now i just need the data to be in the correct headed columns. I've thought about using match/index as the data isn't in a consistent order, but i'm getting nowhere fast. I'd like a separate sheet with the column headings the same as the entries, ie. BYODC, LFD, VIS, WCH, etc. and the room code (left-most column shown below) as the row labels.

[TABLE="class: grid, width: 1229"]
<tbody>[TR]
[TD]ARK109[/TD]
[TD]1 x BYODC[/TD]
[TD] 1 x LFD[/TD]
[TD] 1 x VIS[/TD]
[TD] 1 x WCH[/TD]
[TD] 1 x PC[/TD]
[TD] 2 x WGB[/TD]
[TD] 1 x DVD[/TD]
[TD] 1 x LEC[/TD]
[TD] 1 x SYM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARK110[/TD]
[TD]1 x BYODC[/TD]
[TD] 1 x LFD[/TD]
[TD] 1 x VIS[/TD]
[TD] 1 x WCH[/TD]
[TD] 1 x PC[/TD]
[TD] 2 x WGB[/TD]
[TD] 1 x DVD[/TD]
[TD] 1 x LEC[/TD]
[TD] 1 x SYM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARK111AREA1[/TD]
[TD]1 x BYODV[/TD]
[TD] 1 x VIS[/TD]
[TD] 1 x VRRM[/TD]
[TD] 1 x WCH[/TD]
[TD] 1 x PC[/TD]
[TD] 1 x DP[/TD]
[TD] 1 x VRARM[/TD]
[TD] 1 x VRFM[/TD]
[TD] 1 x LEC[/TD]
[TD] 1 x LCF[/TD]
[TD] 1 x SYM[/TD]
[TD] 1 x BYODH[/TD]
[TD] 1 x BYODW[/TD]
[TD] 1 x DVD[/TD]
[TD] 1 x PRJ[/TD]
[TD] 1 x SPKR[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARK111AREA2[/TD]
[TD]1 x WCH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARK112[/TD]
[TD]1 x BYODC[/TD]
[TD] 1 x VIS[/TD]
[TD] 1 x NET[/TD]
[TD] 1 x WCH[/TD]
[TD] 1 x BYODW[/TD]
[TD] 1 x PC[/TD]
[TD] 1 x DP[/TD]
[TD] 1 x DVD[/TD]
[TD] 1 x LEC[/TD]
[TD] 1 x SPKR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ARK115[/TD]
[TD]1 x BYODV[/TD]
[TD] 1 x VIS[/TD]
[TD] 1 x NET[/TD]
[TD] 1 x VRRM[/TD]
[TD] 1 x WCH[/TD]
[TD] 1 x PC[/TD]
[TD] 1 x DP[/TD]
[TD] 1 x VRARM[/TD]
[TD] 1 x VRFM[/TD]
[TD] 1 x DVD[/TD]
[TD] 1 x FIXT[/TD]
[TD] 1 x BLA[/TD]
[TD] 1 x IL[/TD]
[TD] 1 x LCF[/TD]
[TD] 1 x LEC[/TD]
[TD] 1 x SYM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks very much
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
you mean like this ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]BYODC[/td][td=bgcolor:#70AD47]LFD[/td][td=bgcolor:#70AD47]VIS[/td][td=bgcolor:#70AD47]WCH[/td][td=bgcolor:#70AD47]PC[/td][td=bgcolor:#70AD47]WGB[/td][td=bgcolor:#70AD47]DVD[/td][td=bgcolor:#70AD47]LEC[/td][td=bgcolor:#70AD47]SYM[/td][td=bgcolor:#70AD47]BYODV[/td][td=bgcolor:#70AD47]VRRM[/td][td=bgcolor:#70AD47]DP[/td][td=bgcolor:#70AD47]VRARM[/td][td=bgcolor:#70AD47]VRFM[/td][td=bgcolor:#70AD47]LCF[/td][td=bgcolor:#70AD47]BYODH[/td][td=bgcolor:#70AD47]BYODW[/td][td=bgcolor:#70AD47]PRJ[/td][td=bgcolor:#70AD47]SPKR[/td][td=bgcolor:#70AD47]NET[/td][td=bgcolor:#70AD47]FIXT[/td][td=bgcolor:#70AD47]BLA[/td][td=bgcolor:#70AD47]IL[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ARK109[/td][td=bgcolor:#E2EFDA]1 x BYODC[/td][td=bgcolor:#E2EFDA]1 x LFD[/td][td=bgcolor:#E2EFDA]1 x VIS[/td][td=bgcolor:#E2EFDA]1 x WCH[/td][td=bgcolor:#E2EFDA]1 x PC[/td][td=bgcolor:#E2EFDA]2 x WGB[/td][td=bgcolor:#E2EFDA]1 x DVD[/td][td=bgcolor:#E2EFDA]1 x LEC[/td][td=bgcolor:#E2EFDA]1 x SYM[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ARK110[/td][td]1 x BYODC[/td][td]1 x LFD[/td][td]1 x VIS[/td][td]1 x WCH[/td][td]1 x PC[/td][td]2 x WGB[/td][td]1 x DVD[/td][td]1 x LEC[/td][td]1 x SYM[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ARK111AREA1[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x VIS[/td][td=bgcolor:#E2EFDA]1 x WCH[/td][td=bgcolor:#E2EFDA]1 x PC[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x DVD[/td][td=bgcolor:#E2EFDA]1 x LEC[/td][td=bgcolor:#E2EFDA]1 x SYM[/td][td=bgcolor:#E2EFDA]1 x BYODV[/td][td=bgcolor:#E2EFDA]1 x VRRM[/td][td=bgcolor:#E2EFDA]1 x DP[/td][td=bgcolor:#E2EFDA]1 x VRARM[/td][td=bgcolor:#E2EFDA]1 x VRFM[/td][td=bgcolor:#E2EFDA]1 x LCF[/td][td=bgcolor:#E2EFDA]1 x BYODH[/td][td=bgcolor:#E2EFDA]1 x BYODW[/td][td=bgcolor:#E2EFDA]1 x PRJ[/td][td=bgcolor:#E2EFDA]1 x SPKR[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ARK111AREA2[/td][td][/td][td][/td][td][/td][td]1 x WCH[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]ARK112[/td][td=bgcolor:#E2EFDA]1 x BYODC[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x VIS[/td][td=bgcolor:#E2EFDA]1 x WCH[/td][td=bgcolor:#E2EFDA]1 x PC[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x DVD[/td][td=bgcolor:#E2EFDA]1 x LEC[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x DP[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x BYODW[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]1 x SPKR[/td][td=bgcolor:#E2EFDA]1 x NET[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]ARK115[/td][td][/td][td][/td][td]1 x VIS[/td][td]1 x WCH[/td][td]1 x PC[/td][td][/td][td]1 x DVD[/td][td]1 x LEC[/td][td]1 x SYM[/td][td]1 x BYODV[/td][td]1 x VRRM[/td][td]1 x DP[/td][td]1 x VRARM[/td][td]1 x VRFM[/td][td]1 x LCF[/td][td][/td][td][/td][td][/td][td][/td][td]1 x NET[/td][td]1 x FIXT[/td][td]1 x BLA[/td][td]1 x IL[/td][/tr]
[/table]
 
Upvote 0
so use PowerQuery (Get&Transform)

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
    TAD = Table.AddColumn(Unpivot, "Text After Delimiter", each Text.AfterDelimiter([Value], "x "), type text),
    ROC = Table.SelectColumns(TAD,{"Column1", "Value", "Text After Delimiter"}),
    Pivot = Table.Pivot(ROC, List.Distinct(ROC[#"Text After Delimiter"]), "Text After Delimiter", "Value")
in
    Pivot[/SIZE]
 
Upvote 0
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.

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1ARK1091 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
2ARK1101 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
3ARK111AREA11 x BYODV1 x VIS1 x VRRM1 x WCH1 x PC1 x DP1 x VRARM1 x VRFM1 x LEC1 x LCF1 x SYM1 x BYODH1 x BYODW1 x DVD1 x PRJ1 x SPKR
4ARK111AREA21 x WCH
5ARK1121 x BYODC1 x VIS1 x NET1 x WCH1 x BYODW1 x PC1 x DP1 x DVD1 x LEC1 x SPKR
6ARK1151 x BYODV1 x VIS1 x NET1 x VRRM1 x WCH1 x PC1 x DP1 x VRARM1 x VRFM1 x DVD1 x FIXT1 x BLA1 x IL1 x LCF1 x LEC1 x SYM
7
8
9BYODCLFDVISWCHPCWGBDVDLECSYMBYODVVRRMDPVRARMVRFMLCFBYODHBYODWPRJSPKRNETFIXTBLAIL
10ARK1091 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
11ARK1101 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
12ARK111AREA11 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF1 x BYODH1 x BYODW1 x PRJ1 x SPKR
13ARK111AREA21 x WCH
14ARK1121 x BYODC1 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x DP1 x BYODW1 x SPKR1 x NET
15ARK1151 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF1 x NET1 x FIXT1 x BLA1 x IL
Arrange



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

Excel Workbook
ABCDEFGHIJKLMNOPQRSTUVWX
1ARK1091 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
2ARK1101 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
3ARK111AREA11 x BYODV1 x VIS1 x VRRM1 x WCH1 x PC1 x DP1 x VRARM1 x VRFM1 x LEC1 x LCF1 x SYM1 x BYODH1 x BYODW1 x DVD1 x PRJ1 x SPKR
4ARK111AREA21 x WCH
5ARK1121 x BYODC1 x VIS1 x NET1 x WCH1 x BYODW1 x PC1 x DP1 x DVD1 x LEC1 x SPKR
6ARK1151 x BYODV1 x VIS1 x NET1 x VRRM1 x WCH1 x PC1 x DP1 x VRARM1 x VRFM1 x DVD1 x FIXT1 x BLA1 x IL1 x LCF1 x LEC1 x SYM
7
17
18BYODCLFDVISWCHPCWGBDVDLECSYMBYODVVRRMDPVRARMVRFMLCFBYODHBYODWPRJSPKRNETFIXTBLAIL
19ARK1091 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
20ARK1101 x BYODC1 x LFD1 x VIS1 x WCH1 x PC2 x WGB1 x DVD1 x LEC1 x SYM
21ARK111AREA11 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF1 x BYODH1 x BYODW1 x PRJ1 x SPKR
22ARK111AREA21 x WCH
23ARK1121 x BYODC1 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x DP1 x BYODW1 x SPKR1 x NET
24ARK1151 x VIS1 x WCH1 x PC1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF1 x NET1 x FIXT1 x BLA1 x IL
Arrange
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,828
Messages
6,181,213
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top