Link to home
Create AccountLog in
Avatar of DennisHacker
DennisHackerFlag for United States of America

asked on

Vertical to Horizontal Data in Excel 2013--Large File

I have a large Excel file with about 9000 lines of data.  The data on the sheet is formatted similar to this, but repeats with different names:
User generated image
I need to change this to a format similar to this:
User generated image
I will be doing this for multiple years, so on more than just one file.  Is there a batch function that can be run to do this?
Avatar of Martin Andrews
Martin Andrews

Not pretty, but if you're willing to fiddle around with your spreadsheet a bit you could try something like the attached.  There's likely a better way, but if your stuck you can use this.
transpose.xls
Forgot to mention: if you use this method you'll have to sort by name first!
Actually, this is cleaner.  Note: if you update the formula in column E, be sure to press CTRL + SHIFT + ENTER when finished, as it is an array formula.
transpose2.xlsx
Avatar of DennisHacker

ASKER

Martin:  Thanks for the solution.  If my data set was smaller, I may try that, but I simplified what it looks like for illustration purposes on here.  This file has almost 9000 lines in it, and it's one of the smaller ones.  I'm really looking for some kind of script to extract the data, if that's possible.
You can easily script it in VBA if you allow that...are all your spreadsheets in that two column file format with no empty rows?
SOLUTION
Avatar of Martin Andrews
Martin Andrews

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Tom Farrar
You could do something like add a helper column and then use a pivot table like the attached.
EE.xlsx
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I'll have to edit it because the data isn't that clean...but I get what you did.  Thanks!
Hi Dennis,

Though you have accepted the solution, you may try the below code to transform the data on another tab.
Click the button on Sheet1 to get the desired output.

Sub TransposeData()
Dim sws As Worksheet, dws As Worksheet
Dim i As Long, lc As Long
Dim x, y, z
Dim dict

Application.ScreenUpdating = False

Set sws = Sheets("Sheet1")
On Error Resume Next
Set dws = Sheets("Output")
dws.Cells.Clear
On Error GoTo 0
If dws Is Nothing Then
   Sheets.Add(after:=Sheets(Sheets.Count)).Name = "Output"
   Set dws = ActiveSheet
End If
With dws.Range("A1:B1")
   .Value = Array("Name", "Score 1")
   .Font.Bold = True
   .Font.Size = 12
End With
Set dict = CreateObject("Scripting.Dictionary")
x = sws.Range("A1").CurrentRegion.Value
For i = 2 To UBound(x, 1)
   If Not dict.exists(x(i, 1)) Then
      dict.Item(x(i, 1)) = x(i, 2)
   Else
      dict.Item(x(i, 1)) = dict.Item(x(i, 1)) & ";" & x(i, 2)
   End If
Next i
dws.Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.keys)
y = Application.Transpose(dict.items)
For i = 1 To UBound(y, 1)
   z = Split(y(i, 1), ";")
   dws.Range("B" & i + 1).Resize(1, UBound(z, 1) + 1).Value = Split(y(i, 1), ";")
Next i
lc = dws.UsedRange.Columns.Count
dws.Range("C1").Copy
dws.Range("B2", dws.Cells(UBound(y, 1) + 1, lc)).PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd
dws.Range("B1").AutoFill Destination:=dws.Range("B1", dws.Cells(1, lc)), Type:=xlFillDefault
dws.Range("A1").CurrentRegion.Borders.Color = vbBlack
dws.Columns.AutoFit
dws.Activate
dws.Range("A1").Select
Application.ScreenUpdating = True
End Sub

Open in new window

TransformData_Dennis.xlsm