Vertical source data to horizontal table?

sammipd

Board Regular
Joined
Jun 6, 2010
Messages
63
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 #NameTitle
A101John SmithPresident
D202Sam BrownVP 1
B101Bill GreenVP 2
C202Jeff ThomasVP 3
A101Jeff ThomasTech 1
D202John SmithTech 2
B101Bill GreenTech 2
A101Pete PetersTech 1
D202Gene JohnsonTech 3
Result (over 1200 File #s to match for data)
File #NameTitleNameTitleNameTitle
A101
B101
C202
D202

<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results on sheet2.
Code:
[COLOR="Navy"]Sub[/COLOR] MG19Jun44
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

Ray = Range("A1").CurrentRegion.Resize(, 3)
[COLOR="Navy"]With[/COLOR] 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"

    [COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
            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)
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Ray(n, 1))
                Q(0) = Q(0) + 2
                [COLOR="Navy"]If[/COLOR] UBound(nray, 2) < Q(0) [COLOR="Navy"]Then[/COLOR] 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
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, UBound(nray, 2))
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] With
Regards Mick
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757
adapt PowerQuery example:
(this is NOT copy/paste solution!)

File #NameTitleFile #Name.1Title.1Name.2Title.2Name.3Title.3
A101John SmithPresidentA101John SmithPresidentJeff ThomasTech 1Pete PetersTech 1
D202Sam BrownVP 1B101Bill GreenVP 2Bill GreenTech 2
B101Bill GreenVP 2C202Jeff ThomasVP 3
C202Jeff ThomasVP 3D202Sam BrownVP 1John SmithTech 2Gene JohnsonTech 3
A101Jeff ThomasTech 1
D202John SmithTech 2
B101Bill GreenTech 2
A101Pete PetersTech 1
D202Gene JohnsonTech 3

Code:
[SIZE=1]// 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[/SIZE]
 
Last edited:

sammipd

Board Regular
Joined
Jun 6, 2010
Messages
63
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!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,757

Watch MrExcel Video

Forum statistics

Threads
1,102,135
Messages
5,484,938
Members
407,475
Latest member
Dix_Fix

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top