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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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

Watch MrExcel Video

Forum statistics

Threads
1,099,892
Messages
5,471,331
Members
406,757
Latest member
dizzane

This Week's Hot Topics

Top