# Thread: Vertical source data to horizontal table? Thanks: 0 Likes: 0

1. ## Vertical source data to horizontal table?

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 # Name Title A101 John Smith President D202 Sam Brown VP 1 B101 Bill Green VP 2 C202 Jeff Thomas VP 3 A101 Jeff Thomas Tech 1 D202 John Smith Tech 2 B101 Bill Green Tech 2 A101 Pete Peters Tech 1 D202 Gene Johnson Tech 3 Result (over 1200 File #s to match for data) File # Name Title Name Title Name Title A101 B101 C202 D202

2. ## Re: Vertical source data to horizontal table?

Try this for results on sheet2.
Code:
```Sub MG19Jun44
Dim Ray As Variant, n As Long, Q As Variant, c As Long

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

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

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

3. ## Re: Vertical source data to horizontal table?

(this is NOT copy/paste solution!)

 File # Name Title File # Name.1 Title.1 Name.2 Title.2 Name.3 Title.3 A101 John Smith President A101 John Smith President Jeff Thomas Tech 1 Pete Peters Tech 1 D202 Sam Brown VP 1 B101 Bill Green VP 2 Bill Green Tech 2 B101 Bill Green VP 2 C202 Jeff Thomas VP 3 C202 Jeff Thomas VP 3 D202 Sam Brown VP 1 John Smith Tech 2 Gene Johnson Tech 3 A101 Jeff Thomas Tech 1 D202 John Smith Tech 2 B101 Bill Green Tech 2 A101 Pete Peters Tech 1 D202 Gene Johnson Tech 3

Code:
```// 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```

4. ## Re: Vertical source data to horizontal table?

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!

5. ## Re: Vertical source data to horizontal table?

sure, here is a link to example file which works with your example. However, with more data you need to check if it will work for you.

you need to know:
• Power Query is case sensitive
• Power Query doesn't like changing headers (you should know what you are doing)
• Power Query is a completely different way of thinking and acting

6. ## Re: Vertical source data to horizontal table?

Try this example using your data, when happy with the result on sheet2 you can replace the trial data, with your actual data.
https://app.box.com/s/7gon7ychrfns08f3gesrr2814kj3ww0v