Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Transpose Columns to Rows

Posted on 2012-04-13
Medium Priority
424 Views
Hello Experts,

I have a text file which imports into Excel in the following format:

User No.       Name                 Status                        Access Right
1                    User Name 1     Change password    Access Right 1
3                    User Name 2     Active                        Access Right 1
3                    User Name 2     Active                        Access Right 2
3                    User Name 2     Active                        Access Right 3
111                User Name 4     Change password    Access Right 7
123                User Name 5     Active                        Access Right 1

I would like the file to be formatted as:
User No.    Name        Status     Access Right      Access Right      Access Right   Access Right

I have attached an example of the file I import into Excel and the way I would like it to look.

Infosec
0
Question by:Sonia Bowditch

LVL 24

Assisted Solution

StephenJR earned 664 total points
ID: 37841954
Here is one approach:
``````Sub x()

Dim r As Long, n As Long

Application.ScreenUpdating = False

r = 2

With Sheets("Raw")
.Range("A1:D1").Copy Sheets("Format").Range("A1")
Do Until IsEmpty(.Cells(r, 1))
n = WorksheetFunction.CountIf(.Columns(1), .Cells(r, 1))
.Cells(r, 1).Resize(, 3).Copy Sheets("Format").Range("A" & Rows.Count).End(xlUp)(2)
.Cells(r, 4).Resize(n).Copy
Sheets("Format").Range("A" & Rows.Count).End(xlUp).Offset(1, 3).PasteSpecial Transpose:=True
r = r + n
Loop
End With

Application.ScreenUpdating = True

End Sub
``````
0

LVL 43

Assisted Solution

Saqib Husain, Syed earned 668 total points
ID: 37841964
Here is mine

Sub xformdata()
Dim i As Long
For i = 2 To Range("A1").End(xlDown).Row
If Cells(i, 1) = "" Then Exit For
If Cells(i, 1) = Cells(i + 1, 1) Then
Cells(i, Columns.Count).End(xlToLeft).Offset(0, 1) = Cells(i + 1, 4)
Cells(i + 1, 1).EntireRow.Delete
i = i - 1
End If
Next i
End Sub
0

LVL 93

Accepted Solution

Patrick Matthews earned 668 total points
ID: 37841980
This seems to be working for me:

``````Sub FixIt()

Dim LastRow As Long
Dim arr As Variant
Dim r As Long
Dim DestR As Long, DestC As Long
Dim UserNum As Long
Dim TestUserNum As Long

With ActiveSheet
LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
arr = .Range("a2:d" & LastRow).Value
End With

[a1:d1].Value = Array("User No", "Name", "Status", "Access Right")
DestR = 1

For r = 1 To UBound(arr, 1)
TestUserNum = arr(r, 1)
If TestUserNum <> UserNum Then
DestR = DestR + 1
Cells(DestR, 1) = TestUserNum
Cells(DestR, 2) = arr(r, 2)
Cells(DestR, 3) = arr(r, 3)
UserNum = TestUserNum
DestC = 4
End If
Cells(DestR, DestC) = arr(r, 4)
DestC = DestC + 1
Next

Columns.AutoFit

End Sub
``````
0

LVL 9

Expert Comment

ID: 37842027
Gys, surely a pivot table solution,  from external source if necessary?  (frm phone)
0

Author Closing Comment

ID: 37842033
Thank you Matthewpartrick, ssaqibh and StephenJR.  Your solutions all worked perfectly and did exactly what I needed.

Infosec.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll