Results 1 to 8 of 8

Thread: Sorting help

  1. #1
    Seeker santhosh's Avatar
    Join Date
    Jul 2014
    Posts
    5
    Articles
    0
    Excel Version
    office 10

    Sorting help



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    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 by Bob Phillips; 2014-07-28 at 08:39 AM.

  3. #3
    Seeker santhosh's Avatar
    Join Date
    Jul 2014
    Posts
    5
    Articles
    0
    Excel Version
    office 10
    sir please explain me what i have to do with this code.....
    i have only a little knowledge in excel

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365

  5. #5
    Seeker santhosh's Avatar
    Join Date
    Jul 2014
    Posts
    5
    Articles
    0
    Excel Version
    office 10
    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...

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,846
    Articles
    0
    Excel Version
    O365
    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.

  7. #7
    Seeker santhosh's Avatar
    Join Date
    Jul 2014
    Posts
    5
    Articles
    0
    Excel Version
    office 10
    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

  8. #8
    Seeker santhosh's Avatar
    Join Date
    Jul 2014
    Posts
    5
    Articles
    0
    Excel Version
    office 10
    bob jiiiii please help me

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •