Convert column data into multiple rows

[vpapierman]

I have students who sign up for one or more classes . The data comes to me like this:

Name1 Class1_Indicator Class2_Indicator Class3_Indicator
Sam Y Y Y
John Y N Y

I need to change it for the Teachers to use as rosters to look like this:

Sam Class1
Sam Class2
Sam Class3
John Class1
John Class3

Is this possible using common excel features? Does it have to be done in VB? And either way can I get some guidance. I have programming background but none in VB.

 

[Claus Busch]

Hi,

Am Thu, 6 Mar 2014 09:04:00 -0800 (PST) schrieb (e-mail address removed):

Name1 Class1_Indicator Class2_Indicator Class3_Indicator
Sam Y Y Y
John Y N Y

I need to change it for the Teachers to use as rosters to look like this:

Sam Class1
Sam Class2
Sam Class3
John Class1
John Class3

your data in Sheet1
Then paste following code in a standard module and run the macro Test.
It writes you the result into Sheet2:

Sub Test()
Dim LCol As Integer
Dim LRow As Long
Dim i As Long, j As Long
Dim myCnt As Long

With Sheets("Sheet1")
LRow = .UsedRange.Rows.Count
For i = 2 To LRow
LCol = .Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To LCol
If .Cells(i, j) = "Y" Then
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0) = .Cells(i, 1)
Sheets("Sheet2").Cells(Rows.Count, 2).End(xlUp) _
.Offset(1, 0) = Left(.Cells(1, j), _
InStr(.Cells(1, j), "_") - 1)
End If
Next
Next
End With

End Sub


Regards
Claus B.