Sorting help

S

santhosh

New member
Joined
Jul 28, 2014
Messages
5
Reaction score
0
Points
0
Excel Version(s)
office 10
Sir i have a list of students participating in various items . i am showing a sample below......

sl name music dance paint draw
1 A x x
2 B X
3 C X X x
4 D X X


.
i need a proogramme that if i enter 'X' in the cell corresponding for a student, i have get the list of students who are participating in that event only in the next sheet..... Like that i need to get the consolidated list of each event and its participants..........
MUSIC
sl name
1
2
3

DANCE
sl name
1
2

PAINT
sl name
1
2
3
please help me i resolving my problem.....
thanking you......
santhosh
 

Attachments

  • listabcd.xlsx
    10.4 KB · Views: 9
Code: 
Public Sub Reformat()
Dim lastrow As Long
Dim target As Range
Dim rownum As Long
Dim i As Long, ii As Long


    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 5 To lastrow
        
            For ii = 3 To 6
            
                If LCase(.Cells(i, ii).Value) = "x" Then
                
                    Set target = Worksheets("Sheet2").Columns((ii - 3) * 3 + 2)
                    rownum = target.Cells(3, 1).End(xlDown).Offset(1, 0).Row
                    target.Cells(rownum, 1).Value = .Cells(i, "A").Value
                    target.Cells(rownum, 2).Value = .Cells(i, "B").Value
                End If
            Next ii
        Next i
    End With
End Sub
 
Last edited:
sir please explain me what i have to do with this code.....
i have only a little knowledge in excel
 
nice work.....
i got it....
still i have some problem....
1) i had given only a list of 4 but i have a list of more than 125 students
2)whenever i change "x" in sheet one no cange occurs in sheet 2
3) how do i add more rows...
 
The number of students shouldn't affect it, 125 should work.

When you change the base data, clear the data in rows 5 down of the output sheet and rerun the macro.
 
after making changes in first sheet and re run the macro didnt show any data in the second sheet........earlier it shows correct values bsut now it is totally blank
 
bob jiiiii please help me
 
You must log in or register to reply here.
Back
Top