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
:
: 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]
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
: STUDENT ENGL MATH HIST GEOG CHEM in row 1
:
refer to uploaded file
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
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!
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.
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.
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.