OK.. "assuming" that the columns with the Data are: A, B, C, etc...
You can use:
Option Explicit
Sub xlate()
Dim cnt_students, cnt_rows, newRow, R, C
Dim DataSheet, RepSheet
DataSheet = "Sheet1"
RepSheet = "Sheet2"
cnt_students =
Application.WorksheetFunction.CountA(Sheets(DataSheet).Range("A1").EntireRow)
cnt_rows =
Application.WorksheetFunction.CountA(Sheets(DataSheet).Range("A1").EntireColumn)
Sheets(RepSheet).Cells.ClearContents
Sheets(RepSheet).Range("A1") = "Student"
Sheets(RepSheet).Range("B1") = "Course"
Sheets(RepSheet).Range("C1") = "Hour"
'----------------------------------
newRow = 1
For R = 2 To cnt_rows
For C = 2 To cnt_students
If (Sheets(DataSheet).Cells(R, C) <> "") Then
newRow = newRow + 1
Sheets(RepSheet).Cells(newRow, "A") =
Sheets(DataSheet).Cells(1, C) 'Student
Sheets(RepSheet).Cells(newRow, "B") =
Sheets(DataSheet).Cells(R, C) 'Course
Sheets(RepSheet).Cells(newRow, "C") =
Sheets(DataSheet).Cells(R, 1) 'Hour
End If
Next C
Next R
'----------------------------------
Cells.Select
ActiveWorkbook.Worksheets(RepSheet).Sort.SortFields.Clear
ActiveWorkbook.Worksheets(RepSheet).Sort.SortFields.Add
Key:=Range("A2:A11") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets(RepSheet).Sort.SortFields.Add
Key:=Range("C2:C11") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(RepSheet).Sort
.SetRange Range("A1:I11")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A2").Select
End Sub
I'm using Excel 2007 and recorded the "sort" section.
You may wish to record your own "sort" for the final product
so you get the order you're looking for.
Paul
----- Original Message ----
> From: Joost <dejong.jo...@gmail.com>
> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> Sent: Tue, January 12, 2010 11:16:49 AM
> Subject: $$Excel-Macros$$ grid to list
>
> Hi,
>
> For handling in another program I've been asked to convert our excel-
> student-roster into a list. Currently our roster is a simple grid with
> the hours on one axis and the students on the other:
>
> Hour | StudentA | StudentB | etc.
> H1 | Course A | Course B |
> H2 | Course B | Course C |
> H3 | Course D | Course A |
> H4 | Course B | Course D |
> H5 | Course C | Course C |
>
> The finished list should look something like this:
>
> Student | Course | Hour |
> StudentA | CourseA | H1 |
> StudentA | CourseB | H2 |
> StudentA | CourseD | H3 |
> StudentA | CourseB | H4 |
> StudentB | CourseB | H1 |
> StudentB | CourseC | H2 |
> StudentB | CourseA | H3 |
> StudentB | CourseD | H4 |
>
> There's the option to endure hours of manually creating the list by
> cell-reference, but I guess there's a simpler macro solution. I have
> some macro-experience, but not enough to create an elegant solution;
> that is of course the reason to see if some wizz has a simple one.
>
> - Joost
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us in TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,500 subscribers worldwide and receive many nice notes about
> the
> learning and support from the group.Let friends and co-workers know they can
> subscribe to group at http://groups.google.com/group/excel-macros/subscribe
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us in TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
