Results 1 to 6 of 6

Thread: Help sorting data header cell, header cell etc

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Help sorting data header cell, header cell etc

    Hi All,

    I need to get data in a table to then list using the clumn header then the cell, per row.
    Header1 Header 2 Header 3 Header 4
    FU Bar Hello World
    End Of Days Cheese

    using the table above as an example to display the below

    Header1 "FU"
    Header2 "Bar"
    Header3 "Hello"
    Header4 "World"

    Header1 "End"
    Header2 "Of"
    Header3 "Days"
    Header4 "Cheese"

    The column range will be about 10 columns and the rows will be about 68 however i would like to configure it to different table ranges.

    Can anyone help me sort this out?
    Cheers

    Martyn

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,201
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help sorting data header cell, header cell etc

    Your data must be start on sheet1 in cell A1

    Try this macro:

    Code:
    Sub Sorting_Data()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    sh2.Cells.ClearContents
    uc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To sh1.Range("A" & Rows.Count).End(xlUp).Row
        sh2.Range("A" & Rows.Count).End(xlUp)(2).Resize(uc).Value = Application.Transpose(sh1.Range("A1").Resize(1, uc).Value)
        sh2.Range("B" & Rows.Count).End(xlUp)(2).Resize(uc).Value = Application.Transpose(sh1.Range("A" & i).Resize(1, uc).Value)
    Next
    End Sub
    Regards Dante Amor

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

    Cool Re: Help sorting data header cell, header cell etc

    try PowerQuery (Get&Transform)

    Header1 Header 2 Header 3 Header 4 Attribute Value
    FU Bar Hello World Header1 FU
    End Of Days Cheese Header 2 Bar
    Header 3 Hello
    Header 4 World
    Header1 End
    Header 2 Of
    Header 3 Days
    Header 4 Cheese


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value")
    in
        Unpivot
    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

  4. #4
    New Member
    Join Date
    Apr 2016
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Help sorting data header cell, header cell etc

    Thanks. I will give both of these a try

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

    Default Re: Help sorting data header cell, header cell etc

    You are welcome
    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

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,201
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Help sorting data header cell, header cell etc

    Quote Originally Posted by Martynrbell View Post
    Thanks. I will give both of these a try
    Ok, let me know if you have any doubt.
    Regards Dante Amor

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
  •