Results 1 to 6 of 6

Thread: Vertical source data to horizontal table?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vertical source data to horizontal table?

    My source data includes File #, Name and Title. There are up to 10 Name/Titles associated with each File#, and the Names are often repeated with a different Title in each File. I need to show all the data by File #. Lookup only picks up one data set per file. Can you help? Thank you!

    Source Data (6500+ rows)
    File # Name Title
    A101 John Smith President
    D202 Sam Brown VP 1
    B101 Bill Green VP 2
    C202 Jeff Thomas VP 3
    A101 Jeff Thomas Tech 1
    D202 John Smith Tech 2
    B101 Bill Green Tech 2
    A101 Pete Peters Tech 1
    D202 Gene Johnson Tech 3
    Result (over 1200 File #s to match for data)
    File # Name Title Name Title Name Title
    A101
    B101
    C202
    D202

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Vertical source data to horizontal table?

    Try this for results on sheet2.
    Code:
    Sub MG19Jun44
    Dim Ray As Variant, n As Long, Q As Variant, c As Long
    
    Ray = Range("A1").CurrentRegion.Resize(, 3)
    With CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
       ReDim nray(1 To UBound(Ray, 1), 1 To 3)
    c = 1
         nray(1, 1) = "File#"
         nray(1, 2) = "Name"
         nray(1, 3) = "Title"
    
        For n = 2 To UBound(Ray, 1)
            If Not .Exists(Ray(n, 1)) Then
                c = c + 1
                nray(c, 1) = Ray(n, 1)
                nray(c, 2) = Ray(n, 2)
                nray(c, 3) = Ray(n, 3)
                .Add Ray(n, 1), Array(3, c)
            Else
                Q = .Item(Ray(n, 1))
                    Q(0) = Q(0) + 2
                    If UBound(nray, 2) < Q(0) Then ReDim Preserve nray(1 To UBound(Ray, 1), 1 To Q(0))
                    nray(1, Q(0) - 1) = "Name"
                    nray(1, Q(0)) = "Title"
                    nray(Q(1), Q(0) - 1) = Ray(n, 2)
                    nray(Q(1), Q(0)) = Ray(n, 3)
                .Item(Ray(n, 1)) = Q
            End If
    Next
    End With
    
    With Sheets("Sheet2").Range("A1").Resize(c, UBound(nray, 2))
        .Value = nray
        .Borders.Weight = 2
        .Columns.AutoFit
    End With
    Regards Mick

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

    Cool Re: Vertical source data to horizontal table?

    adapt PowerQuery example:
    (this is NOT copy/paste solution!)

    File # Name Title File # Name.1 Title.1 Name.2 Title.2 Name.3 Title.3
    A101 John Smith President A101 John Smith President Jeff Thomas Tech 1 Pete Peters Tech 1
    D202 Sam Brown VP 1 B101 Bill Green VP 2 Bill Green Tech 2
    B101 Bill Green VP 2 C202 Jeff Thomas VP 3
    C202 Jeff Thomas VP 3 D202 Sam Brown VP 1 John Smith Tech 2 Gene Johnson Tech 3
    A101 Jeff Thomas Tech 1
    D202 John Smith Tech 2
    B101 Bill Green Tech 2
    A101 Pete Peters Tech 1
    D202 Gene Johnson Tech 3


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Group = Table.Group(Source, {"File #"}, {{"Count", each _, type table}}),
        List1 = Table.AddColumn(Group, "Name", each Table.Column([Count],"Name")),
        List2 = Table.AddColumn(List1, "Title", each Table.Column([Count],"Title")),
        Extract1 = Table.TransformColumns(List2, {"Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        Extract2 = Table.TransformColumns(Extract1, {"Title", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
        Split1 = Table.SplitColumn(Extract2, "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2", "Name.3"}),
        Split2 = Table.SplitColumn(Split1, "Title", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Title.1", "Title.2", "Title.3"}),
        ROC = Table.SelectColumns(Split2,{"File #", "Name.1", "Title.1", "Name.2", "Title.2", "Name.3", "Title.3"}),
        Sort = Table.Sort(ROC,{{"File #", Order.Ascending}})
    in
        Sort
    Last edited by sandy666; Jun 19th, 2019 at 12:35 PM.
    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
    Board Regular
    Join Date
    Jun 2010
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vertical source data to horizontal table?

    Thank you both! However, I know very little about VBA or Power Query.
    MickG – I received an error message pointing to the first line of the code. How can I fix this?
    Sandy666 – Can you point me to a quick tutorial to enter your solution? All I have found are not “quick” and my project is due next week.
    Thank you again!

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

    Cool Re: Vertical source data to horizontal table?

    sure, here is a link to example file which works with your example. However, with more data you need to check if it will work for you.

    you need to know:
    • Power Query is case sensitive
    • Power Query doesn't like changing headers (you should know what you are doing)
    • Power Query is a completely different way of thinking and acting


    some links:
    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
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,837
    Post Thanks / Like
    Mentioned
    26 Post(s)
    Tagged
    12 Thread(s)

    Default Re: Vertical source data to horizontal table?

    Try this example using your data, when happy with the result on sheet2 you can replace the trial data, with your actual data.
    https://app.box.com/s/7gon7ychrfns08f3gesrr2814kj3ww0v

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
  •