Vertical source data to horizontal table?

sammipd

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,759
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top