?
Solved

Transpose Columns to Rows

Posted on 2012-04-13
5
Medium Priority
?
401 Views
Last Modified: 2012-04-13
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
0
Comment
Question by:Sonia Bowditch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Assisted Solution

by:StephenJR
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")
    .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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
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

by:
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
    
    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
 
LVL 9

Expert Comment

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

Author Closing Comment

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

Infosec.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question