How to covert from rows to columns

I have an Excel file which look like this:

student 1 Eng A
student 1 Math B
Student 1 Hist B
Student 1 Geog C
Student 2 Eng D
Student 2 Math C
Student 2 chem D

I would like to convert it into the following format:

Eng Math Hist Geog Chem
student 1 A B B C
student 2 D C D

Can any it be done in VBA?

Comments

  • : I have an Excel file which look like this:
    :
    : student 1 Eng A
    : student 1 Math B
    : Student 1 Hist B
    : Student 1 Geog C
    : Student 2 Eng D
    : Student 2 Math C
    : Student 2 chem D
    :
    : I would like to convert it into the following format:
    :
    : Eng Math Hist Geog Chem
    : student 1 A B B C
    : student 2 D C D
    :
    : Can any it be done in VBA?
    :
    :

    this code assumes that the student name,class,grade are stored in separate cells - if in a single cell then you need to parse for each value

    the student data should be in "Sheet1" and outputs result to "Sheet2"

    note- change the [maxRows] constant to the max rows in your worksheet


    [code]
    Type Student
    Name As String
    Engl As String
    Math As String
    Hist As String
    Geog As String
    Chem As String
    End Type

    '** max rows of students **
    '** CHANGE to no. of rows **
    Const maxRows = 7
    '***************************

    Dim mStudents(1 To maxRows) As Student
    Dim mNames(1 To maxRows) As String


    Sub Macro1()
    '
    ' Keyboard Shortcut: Ctrl+b
    '
    Dim row As Integer
    Dim col As Integer
    Dim wb As Workbook
    Dim ws As Worksheet

    Dim txt As String
    Dim sNames As String
    Dim sClass As String
    Dim sGrade As String
    Dim ArrayIdx As Integer

    Set wb = Application.ActiveWorkbook
    Set ws = wb.ActiveSheet

    'loop thru rows
    For row = 1 To maxRows
    'get cell values
    sNames = Trim(UCase(ws.Rows.Cells(row, 1)))
    sClass = Trim(UCase(ws.Rows.Cells(row, 2)))
    sGrade = Trim(UCase(ws.Rows.Cells(row, 3)))

    'store students data in array
    For ArrayIdx = 1 To maxRows
    mStudents(row).Name = sNames
    Select Case sClass
    Case "ENG": mStudents(row).Engl = sGrade
    Case "MATH": mStudents(row).Math = sGrade
    Case "HIST": mStudents(row).Hist = sGrade
    Case "GEOG": mStudents(row).Geog = sGrade
    Case "CHEM": mStudents(row).Chem = sGrade
    End Select
    Next ArrayIdx
    Next row

    Set ws = Nothing
    Set wb = Nothing

    UniqueNames
    PutWorksheet
    End Sub


    Sub PutWorksheet()
    Dim row As Integer
    Dim col As Integer
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim txt As String

    Set wb = Application.ActiveWorkbook
    Set ws = wb.Sheets("Sheet2")

    'column titles
    ws.Rows.Cells(1, 1) = "STUDENT"
    ws.Rows.Cells(1, 2) = "ENGL"
    ws.Rows.Cells(1, 3) = "MATH"
    ws.Rows.Cells(1, 4) = "HIST"
    ws.Rows.Cells(1, 5) = "GEOG"
    ws.Rows.Cells(1, 6) = "CHEM"

    'put names in new worksheet
    For row = 1 To maxRows
    ws.Rows.Cells(row + 1, 1) = mNames(row)
    Next row

    'put grades next to student names
    Dim myRow As Integer
    For row = 1 To maxRows
    For myRow = 1 To maxRows
    If mStudents(row).Name = ws.Rows.Cells(myRow + 1, 1) Then
    If mStudents(row).Engl <> "" Then ws.Rows.Cells(myRow + 1, 2) = mStudents(row).Engl
    If mStudents(row).Math <> "" Then ws.Rows.Cells(myRow + 1, 3) = mStudents(row).Math
    If mStudents(row).Hist <> "" Then ws.Rows.Cells(myRow + 1, 4) = mStudents(row).Hist
    If mStudents(row).Geog <> "" Then ws.Rows.Cells(myRow + 1, 5) = mStudents(row).Geog
    If mStudents(row).Chem <> "" Then ws.Rows.Cells(myRow + 1, 6) = mStudents(row).Chem
    End If
    Next myRow
    Next row

    Set ws = Nothing
    Set wb = Nothing
    End Sub


    Sub UniqueNames()
    Dim i As Integer
    Dim j As Integer
    Dim Name1 As String
    Dim Name2 As String

    Name1 = ""
    j = 0

    For i = 1 To maxRows
    Name2 = mStudents(i).Name
    'put unique names into array
    If Name1 <> Name2 Then
    j = j + 1
    mNames(j) = Name2
    Name1 = Name2
    End If
    Next i
    End Sub
    [/code]
  • Dear Sir

    I tried your code, but it just shows
    STUDENT ENGL MATH HIST GEOG CHEM in row 1

    I have the similar case but the data came from cells instead of need to redefine them.

    Basketball match score result:

    (A1) 1 (B1) ABC (C1) 90
    (A2) 1 (B2) DEF (C2) 80
    (A3) 2 (B3) ABC (C3) 70
    (A3) 2 (B3) DEF (C3) 60

    It means how to convert column data to row and column.

    (A1) Empty (B1) 1 (C1) 2
    (A2) ABC (B2) 90 (C2) 70
    (A3) DEF (B3) 80 (C3) 60

    (A:A) needs to sort by name


    How should I program it with VBA? I use excel 2000
  • : I tried your code, but it just shows
    : STUDENT ENGL MATH HIST GEOG CHEM in row 1
    :

    refer to uploaded file
  • Hi Sir;

    I have a set of data in rows and I have to convert them into columns.
    The set of data I have is from A1 to A24
    and this set continues for 184 rows. I need to convert each row to a column in Excel inorder to graph my data.

    Could you please help me with this?
    a sample of my data is as follows:


    26 28 28 28 32 37 35 33 39 42 45 45 46 47 47 45 33 26 25 19 23 23 28
    26 31 33 32 34 37 35 32 33 31 31 30 28 27 28 27 26 29 33 36 38 39 38
    35 37 38 39 39 39 39 38 37 37 36 37 37 37 36 36 39 41 43 42 39 39 40
    39 33 29 28 31 28 25 28 30 36 42 46 48 48 45 46 46 42 36 25 14 10 8
    6 5 1 1 1 2 2 4 6 19 36 40 39 35 35 34 34 26 30 28 30 32 33
    37 37 32 20 12 8 10 22 28 37 40 42 40 45 46 40 40 43 49 32 24 44 48
    49 44 41 35 28 10 8 13 14 6 5 3 8 29 41 42 43 41 37 24 19 14 6

    It should be noted that each row contains 24 values. forexample, last row starts from 49 and ends with 6.

    I use excel 2007
  • fairly simple to modify the previous code, see uploaded file-
  • Hello forum,

    The row_to_column macro is excellent, however, i would like to modify it so that each row of data is transposed into only 2 columnS, A and B.

    So if row 1 has values of 5 10 5 10 2 4 6 _
    and row 2 has values of 5 10 _ 8 _ 3 5 _

    the end result is this:
    5 10 'from row 1
    5 10 'from row 1
    2 4 'from row 1
    6 _ 'last values from row 1
    5 10 'from row 2
    _ 8 'from row 2
    _ 3 'from row 2
    5 _ 'last values from row 2

    Notes - the underscore represents a blank and must be preserved
    - each value is in its own cell

    Any help here would be killer.

    Thanks!

  • This post has been deleted.
  • Thank you so much for your rows to columns example. It was really helpful. I'm doing something similar, and I've tried (and failed) to tweak your code to fit. Mine is data points from a piece of equipment pulled in from a text file, and the format is kind of weird. I've attached what I'm talking about. I need it to be formatted into columns of each channel (0 through 15), like this:

    Time Ch0 Ch1 Ch2 Ch3 Ch4 Ch5 Ch6 Ch7 ... etc
    15ms 15 63 49 58 51 65 74 14
    30ms 77 49 65 12 34 55 1 98

    My problem is that I can't get the code to skip every 16th row that just says Frame #, and I also can't get it to start a new row every time there's a new time. If I could get any input at all on this problem, I'd really appreciate it.

    I've used VBA and Matlab in a few classes before, but I just can't seem to figure this one out.
  • Thank you so much for your rows to columns example. It was really helpful. I'm doing something similar, and I've tried (and failed) to tweak your code to fit. Mine is data points from a piece of equipment pulled in from a text file, and the format is kind of weird. I've attached what I'm talking about. I need it to be formatted into columns of each channel (0 through 15), like this:

    Time Ch0 Ch1 Ch2 Ch3 Ch4 Ch5 Ch6 Ch7 ... etc
    15ms 15 63 49 58 51 65 74 14
    30ms 77 49 65 12 34 55 1 98

    My problem is that I can't get the code to skip every 16th row that just says Frame #, and I also can't get it to start a new row every time there's a new time. If I could get any input at all on this problem, I'd really appreciate it.

    I've used VBA and Matlab in a few classes before, but I just can't seem to figure this one out.
  • Thank you so much for your rows to columns example. It was really helpful. I'm doing something similar, and I've tried (and failed) to tweak your code to fit. Mine is data points from a piece of equipment pulled in from a text file, and the format is kind of weird. I've attached what I'm talking about. I need it to be formatted into columns of each channel (0 through 15), like this:

    Time Ch0 Ch1 Ch2 Ch3 Ch4 Ch5 Ch6 Ch7 ... etc
    15ms 15 63 49 58 51 65 74 14
    30ms 77 49 65 12 34 55 1 98

    My problem is that I can't get the code to skip every 16th row that just says Frame #, and I also can't get it to start a new row every time there's a new time. If I could get any input at all on this problem, I'd really appreciate it.

    I've used VBA and Matlab in a few classes before, but I just can't seem to figure this one out.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories