Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Transpose Columns to Rows

Posted on 2012-04-13
5
Medium Priority
?
418 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

604 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