Solved

Transpose Columns to Rows

Posted on 2012-04-13
5
374 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
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now