Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts

    Sorting question/issue

    I'm working on a grade book application for a teacher friend of mine.
    On a "master student" sheet are the names of the students at the beginning of the semester.
    They are alphabetical by last name.

    There are several other sheets in the workbook that each relate to a course.
    The students' names are pulled from the master sheet into the courses sheets: =Students!A2 etc. and filled down.
    Grades are filled in for students in the student row and various columns as tests and quizzes are taken.

    Lo and behold, part way through the semester a student gets married and the last name changes.
    We couldn't talk them out of marriage, so the original Students sheet needs to re-alphabetize the names by last name.

    Unfortunately, that messes up all of the course sheets. An absolute reference doesn't solve the problem either (=Students!$A$2).

    For all sorts of applications, this type of issue seems likely to crop up often.
    There must be a solution that I haven't thought of (other than urging students not to get married) without having to mess with all 15 course sheets.

    Yes/no/maybe?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    Here's my solution:
    1. Add a Student Number Column on the Master.
    2. Create a NamedRange for the Master. (StudentMaster)
    3. Use VLookup on the course sheets to retrieve name by Student Number.


    Initial Setup of Master:
    InitialEntry.PNG

    Sort Master by Name:
    SortedByName.PNG

    Place Students on Course Sheet by Number using Vlookup to retrieve name.
    CourseInitial.PNG

    Sort Course by Name:
    CourseSortbyName.PNG

    Change Name (I got this far and realized I didn't use any female first names so I just picked one to change as it doesn't affect the overall outcome )
    NameChanged.PNG

    Resort Master List by Name:
    MasterReSorted.PNG

    Name Reflected on Course Sheet:
    NameReflectedinCourse.PNG

    Resort Course by Name:
    CourseResortedbyName.PNG

    I went through all the screen captures just to demonstrate that the name follows through all changes.

    Test File: Kevin-CourseBook.xlsx

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2016-06-23)

  4. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Initially, from your workbook, that looks good. But when I put grades in the 2nd sheet, then sorted the MASTER, they didn't follow the right person.

    In my MASTER sheet, I have a sequential series of numbers associated w/each student initially in alphabetical order.

    Those students are also on each of the course sheets, their name pulled from the master.
    Along w/each student are various grades.

    Suppose person "Jones, Mary" becomes "Smith, Mary" and I change her last name in the MASTER.
    I want to re-alphabetize the MASTER and have the courses re-alphabetized student names as well, along with their respective grades.

    I must have done something wrong because that didn't work.

    If it matters, the full name is on the MASTER in D3 down (say, 20 students), and the sequential numbers are in column E, respectively.

    On EACH course sheet, the full name is in column C, followed by the grades. Start in row 9.
    I put the initial sequence of numbers in column E (parallel with all names starting in row 9).

    The complexity as I see it is that all of the rows (and all respective columns) need to be reordered in the course sheets based on the new alphabetical order of names.
    Last edited by kweaver; 2016-06-23 at 17:29.

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I think I know what I need but can't write it.

    A macro that would contain a list of sheets to be sorted, all the same range.

    It sounds like a CASE statement, but I cannot for the life of me write it.

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by kweaver View Post
    If it matters, the full name is on the MASTER in D3 down (say, 20 students), and the sequential numbers are in column E, respectively.
    Kevin,

    Yes it does matter. For VLookup to work the lookup column (student no.) must be the left most column in the named range. The number in the VLookup formula (=VLOOKUP(A2,StudentMaster,2,FALSE)), in this case 2, is the relative position in the row where the Name can be found.

    You must also make sure that when you do the sort that you select the entire range on the course sheets or just a single cell in the table (like the first actual name). If you select the Names column or just all the Names it won't work!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    I understand, RG...thanks!

    I think this will become more work for the teacher(s) than it's worth. The courses (worksheets) each have a separate password to un-protect them and only the department head (for whom I'm doing this) knows all the passwords. So, she'd have to un-protect each of them, one by one, then run this.

    I think we might take the rare cases where the name changes and brute force our way through it. SIGH.

    Thanks, tho...appreciate the code and the clear explanation.

    BTW: this code that I patched together while searching for a solution seems to do the trick on the un-protected sheets (they all begin with the same 4-letters). However, I'm still left with dealing with the first master sheet because the course sheets still use a reference.

    Sub ReAlpha()
    Dim wsSheet As Worksheet
    Dim LastRow As Long

    For Each wsSheet In Worksheets

    If Left(wsSheet.Name, 4) = "PHYA" Then

    With wsSheet
    .Range("A8:Z28").SORT Key1:=.Range("A8"), Order1:=xlAscending, _
    Key2:=.Range("B8"), Order2:=xlAscending, Header:=xlYes
    End With
    End If

    Next wsSheet

    End Sub
    Last edited by kweaver; 2016-06-23 at 20:07.

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    It would be easy enough to provide the department head with a special password that would allow them access to a PW protected macro that loops through the sheets using sheet name to look up the appropriate password in a table to unlock the sheet for updating. Of course to maintain secuity the VBA project would have to be hidden and password protected.

    If you are interested in this approach let me know and I'll work on a sample.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Absolutely, would LOVE to continue seeing your skills on display! [as would others, I'm sure]

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    Ok, watching Battle Bots so I'll get back to you tomorrow so I can give the solution my full attention.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Kevin,

    Ok, here's an example of what I was talking about.

    Things I didn't do but I'm sure you could implement.
    1. It doesn't sort the MasterSheet but then again it isn't protected either.
    2. Provide buttons on course sheets to individually unprotect them for the instructors, you have the code example to do that if desired. With minor modifications you can use the same userform for gathering the password.
    3. I didn't hide the VBA project so you could easily see the code but you just need to go to the Tools->VBA Project Properties menus and click the Protection Tab. Then check the Lock Project for Viewing box and supply the passwords, I'd suggest using the same password as the Master Password (one less to remember unless you don't trust the holder of that password).


    Here's the Sample File: Kevin-CourseBook RGV2.xlsm

    Don't forget to look at all the modules and the code for the userform too! It's all necessary to the operation of the workbook.


    Post back if you have any questions.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kweaver (2016-06-24)

  13. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    Very interesting, RG. Thanks for the effort. So far, testing seems fine. YAY!

    Have a great weekend in SC.

Posting Permissions

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