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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

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,830
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,830

Watch MrExcel Video

Forum statistics

Threads
1,102,542
Messages
5,487,473
Members
407,603
Latest member
jortronm

This Week's Hot Topics

Top