Vertical source data to horizontal table?

S

sammipd

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

[TABLE="width: 552"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 2"]Source Data (6500+ rows)[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File #[/TD]
[TD]Name[/TD]
[TD]Title[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD]John Smith[/TD]
[TD]President[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D202[/TD]
[TD]Sam Brown[/TD]
[TD]VP 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B101[/TD]
[TD]Bill Green[/TD]
[TD]VP 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C202[/TD]
[TD]Jeff Thomas[/TD]
[TD]VP 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD]Jeff Thomas[/TD]
[TD]Tech 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D202[/TD]
[TD]John Smith[/TD]
[TD]Tech 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B101[/TD]
[TD]Bill Green[/TD]
[TD]Tech 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD]Pete Peters[/TD]
[TD]Tech 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D202[/TD]
[TD]Gene Johnson[/TD]
[TD]Tech 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]Result (over 1200 File #s to match for data)[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]File #[/TD]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]Name[/TD]
[TD]Title[/TD]
[TD]Name[/TD]
[TD]Title[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A101[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B101[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C202[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D202[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
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!)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]File #[/td][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Title[/td][td][/td][td=bgcolor:#70AD47]File #[/td][td=bgcolor:#70AD47]Name.1[/td][td=bgcolor:#70AD47]Title.1[/td][td=bgcolor:#70AD47]Name.2[/td][td=bgcolor:#70AD47]Title.2[/td][td=bgcolor:#70AD47]Name.3[/td][td=bgcolor:#70AD47]Title.3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A101[/td][td=bgcolor:#DDEBF7]John Smith[/td][td=bgcolor:#DDEBF7]President[/td][td][/td][td=bgcolor:#E2EFDA]A101[/td][td=bgcolor:#E2EFDA]John Smith[/td][td=bgcolor:#E2EFDA]President[/td][td=bgcolor:#E2EFDA]Jeff Thomas[/td][td=bgcolor:#E2EFDA]Tech 1[/td][td=bgcolor:#E2EFDA]Pete Peters[/td][td=bgcolor:#E2EFDA]Tech 1[/td][/tr]

[tr=bgcolor:#FFFFFF][td]D202[/td][td]Sam Brown[/td][td]VP 1[/td][td][/td][td]B101[/td][td]Bill Green[/td][td]VP 2[/td][td]Bill Green[/td][td]Tech 2[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B101[/td][td=bgcolor:#DDEBF7]Bill Green[/td][td=bgcolor:#DDEBF7]VP 2[/td][td][/td][td=bgcolor:#E2EFDA]C202[/td][td=bgcolor:#E2EFDA]Jeff Thomas[/td][td=bgcolor:#E2EFDA]VP 3[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]C202[/td][td]Jeff Thomas[/td][td]VP 3[/td][td][/td][td]D202[/td][td]Sam Brown[/td][td]VP 1[/td][td]John Smith[/td][td]Tech 2[/td][td]Gene Johnson[/td][td]Tech 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]A101[/td][td=bgcolor:#DDEBF7]Jeff Thomas[/td][td=bgcolor:#DDEBF7]Tech 1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]D202[/td][td]John Smith[/td][td]Tech 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]B101[/td][td=bgcolor:#DDEBF7]Bill Green[/td][td=bgcolor:#DDEBF7]Tech 2[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]A101[/td][td]Pete Peters[/td][td]Tech 1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]D202[/td][td=bgcolor:#DDEBF7]Gene Johnson[/td][td=bgcolor:#DDEBF7]Tech 3[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

J
  • Question Question
Populate Table with filtered data from another table.
Replies
3
Views
3K
DanteAmor
DanteAmor
B
  • Question Question
Need help to conditionally format a cell in one cell of a table based on the values of two other tables
Replies
3
Views
937
DanteAmor
DanteAmor
G
  • Question Question
Data validation drop down list source to lookup a value and list unused items ?
Replies
0
Views
1K
gaftalik
G
Sean15
  • Question Question
Lookup and Find
Replies
3
Views
606
Aladin Akyurek
Aladin Akyurek
Outdoorsman80
  • Solved
Lookup or match data... not sure how to title this
Replies
4
Views
1K
jtakw
jtakw

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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