Solved

Transpose Columns to Rows

Posted on 2012-04-13
5
391 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 166 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 167 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 92

Accepted Solution

by:
Patrick Matthews earned 167 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

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 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