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:
I need to change this to a format similar to this:
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?
I need to change this to a format similar to this:
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?
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
transpose2.xlsx
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You could do something like add a helper column and then use a pivot table like the attached.
EE.xlsx
EE.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
transpose.xls