Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Transpose 1300 rows at once

    I inherited a excel sheet that is set up wrong in order to use it as a database file. There is employee name and then 59 areas of expertise that you say yes or no to. Basically I need the data in the rows to in a column with criteria and name following the true false.

    I feel im explaining this horribly so I attached how it is now (book1) and how i need it to be (book2). I can do this manually but its a ton of work. Anyone able to help me would be VERY much appreciated.
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached Excel 2003 file with a macro converts your data into the order
    that you want. Your PDF sample shows only 58 TRUE/FALSE items. If there
    are in fact 59 items, then you would change the code that assigns 98 to the
    variable criteriaEndCol, to 99.
    Code:
    Sub Transpose_Records()
    'converts vertical data in Sheet1 to horizontal data in Sheet2
    Dim LastRow As Long
    Dim j As Long, DestColTracker As Integer, SourceRowTracker As Long
    Dim nCounter As Integer, DestRowTracker As Long
    Dim CriteriaCount As Integer
    Dim wks As Worksheet
    
    'criteria columns run from AO through CT. These column hold 58 TRUE or FALSE values, as per the example PDF
    Const criteriaStartCol As Integer = 41      '41 represents column AO
    Const criteriaEndCol As Integer = 98        '98 represents column CT
    
        Set wks = Sheets("Sheet2")
        
        CriteriaCount = criteriaEndCol - criteriaStartCol + 1
        LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
        
        With Sheets(wks.Name)
        
            .Columns("A:CU").ClearContents
            .Cells(1, 1).Value = "Title"
            .Cells(1, 2).Value = "First Name"
            .Cells(1, 3).Value = "Last Name"
            
            nCounter = 0
            For DestColTracker = criteriaStartCol To (criteriaStartCol + CriteriaCount - 1)
                nCounter = nCounter + 1
                .Cells(1, DestColTracker).Value = "Criteria " & nCounter    'add headings in criteria columns
            Next
        
            DestRowTracker = 1
            SourceRowTracker = 2
            
            For j = 1 To LastRow
                DestRowTracker = DestRowTracker + 1
                .Cells(DestRowTracker, 2).Value = Sheets("Sheet1").Cells(j + 1, 2).Value
                .Cells(DestRowTracker, 3).Value = Sheets("Sheet1").Cells(j + 1, 3).Value
                
                For DestColTracker = criteriaStartCol To criteriaEndCol
                    .Cells(DestRowTracker, DestColTracker).Value = Sheets("Sheet1").Cells(SourceRowTracker, 4).Value
                    SourceRowTracker = SourceRowTracker + 1
                Next
                j = j + CriteriaCount
                
            Next
            
        End With
        
        Sheets(wks.Name).Activate
        
        Set wks = Nothing
    
    End Sub
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    I really appreciate the help JerryDal. The code works great on my worksheet but its not doing what I was hoping for and that might be my fault. In book one that I attached is how the layout is now. You have employee 1 listed once with columns for criteria which have a value of yes/no. I need the employees name multiple times/once for each criteria. The code you did works like a group query in access and I can see myself being able to use it in the future and again I thank you a ton for the help!

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I think I got this right on the 2nd try. A new file is attached to
    convert 60 columns of data to be displayed vertically in 4 columns.
    Code:
    Sub Convert_HorizToVert()
    'display 60 columns of data from Sheet1 vertically in 4 columns in Sheet2
    Dim j As Long, destinationRow As Long, LastRow As Long
    Dim sourceColumn As Integer
    Dim firstName As String, lastName As String
    Dim wks As Worksheet
    
    'criteria columns run from AO through CT. These columns hold 58 TRUE or FALSE values, as per the example PDF
    Const criteriaStartCol As Integer = 41      '41 represents column AO
    Const criteriaEndCol As Integer = 98        '98 represents column CT
    
        Set wks = Sheets("Sheet2")
        
        LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
        
        With Sheets(wks.Name)
        
            .Columns("A:D").ClearContents
            .Cells(1, 1).Value = "Capability"
            .Cells(1, 2).Value = "Fname"
            .Cells(1, 3).Value = "Lname"
            .Cells(1, 4).Value = "Status"
        
            destinationRow = 2
            
            For j = 1 To LastRow - 1
                firstName = Sheets("Sheet1").Cells(j + 1, 2).Value
                lastName = Sheets("Sheet1").Cells(j + 1, 3).Value
                For sourceColumn = criteriaStartCol To criteriaEndCol
                    .Cells(destinationRow, 1).Value = Sheets("Sheet1").Cells(1, sourceColumn).Value
                    .Cells(destinationRow, 2).Value = firstName
                    .Cells(destinationRow, 3).Value = lastName
                    .Cells(destinationRow, 4).Value = Sheets("Sheet1").Cells((j + 1), sourceColumn).Value
                    destinationRow = destinationRow + 1
                Next
            Next
            
        End With
        
        Sheets(wks.Name).Activate
        
        Set wks = Nothing
    
    End Sub
    Attached Files Attached Files

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    this may have just saved me a crap ton of time, thank you so much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •