Transpose Columns to Rows

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.

Thank you in advance
Infosec
Test-Spreadsheet.xls
Sonia BowditchInformation Security OfficerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
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")
    .Range("A1").CurrentRegion.Sort key1:=.Range("A1"), order1:=xlAscending, header:=xlYes
    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

Open in new window

0
Saqib Husain, SyedEngineerCommented:
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
Patrick MatthewsCommented:
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
    
    Worksheets.Add
    
    [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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony MellorChartered AccountantCommented:
Gys, surely a pivot table solution,  from external source if necessary?  (frm phone)
0
Sonia BowditchInformation Security OfficerAuthor Commented:
Thank you Matthewpartrick, ssaqibh and StephenJR.  Your solutions all worked perfectly and did exactly what I needed.

Infosec.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.