Results 1 to 7 of 7

Thread: Sorting data into headed columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Sorting data into headed columns

    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.

    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
    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
    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
    ARK111AREA2 1 x WCH
    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
    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

    Thanks very much

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,812
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Sorting data into headed columns

    you mean like this ?

    Column1 BYODC LFD VIS WCH PC WGB DVD LEC SYM BYODV VRRM DP VRARM VRFM LCF BYODH BYODW PRJ SPKR NET FIXT BLA IL
    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
    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
    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
    ARK111AREA2 1 x WCH
    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
    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting data into headed columns

    yes, that's it exactly.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,812
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Sorting data into headed columns

    so use PowerQuery (Get&Transform)

    Code:
    // 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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,974
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sorting data into headed columns

    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

    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
    9 BYODCLFDVISWCHPCWGBDVDLECSYMBYODVVRRMDPVRARMVRFMLCFBYODHBYODWPRJSPKRNETFIXTBLAIL
    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
    12ARK111AREA1 1 x VIS1 x WCH1 x PC 1 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
    13ARK111AREA2 1 x WCH
    14ARK1121 x BYODC 1 x VIS1 x WCH1 x PC 1 x DVD1 x LEC 1 x DP 1 x BYODW 1 x SPKR1 x NET
    15ARK115 1 x VIS1 x WCH1 x PC 1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF 1 x NET1 x FIXT1 x BLA1 x IL

    Spreadsheet Formulas
    CellFormula
    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

    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
    18 BYODCLFDVISWCHPCWGBDVDLECSYMBYODVVRRMDPVRARMVRFMLCFBYODHBYODWPRJSPKRNETFIXTBLAIL
    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
    21ARK111AREA1 1 x VIS1 x WCH1 x PC 1 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
    22ARK111AREA2 1 x WCH
    23ARK1121 x BYODC 1 x VIS1 x WCH1 x PC 1 x DVD1 x LEC 1 x DP 1 x BYODW 1 x SPKR1 x NET
    24ARK115 1 x VIS1 x WCH1 x PC 1 x DVD1 x LEC1 x SYM1 x BYODV1 x VRRM1 x DP1 x VRARM1 x VRFM1 x LCF 1 x NET1 x FIXT1 x BLA1 x IL


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Sorting data into headed columns

    Thanks Peter, finally got it to work!

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,974
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Sorting data into headed columns

    Quote Originally Posted by VEESORS_2 View Post
    Thanks Peter, finally got it to work!
    You're welcome. Glad you got it sorted.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •