+ Reply to Thread
Results 1 to 10 of 10

Give number as per students' merit automatically

  1. #1
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Give number as per students' merit automatically

    Dear friends,
    There are a total of marks in column ‘AQ’ from cell ‘AQ8’. The end range is not fixed because it’s depending upon the number of students in the class which varies from class to class.

    There is a remark in column ‘BE’ from cell ‘BE8’ and the end range is not fixed as mentioned above.
    If the student is failed in 1 subject then the remark is ‘F1’
    If the student is failed in 2 subjects then the remark is ‘F2’
    If the student is failed in 3 subjects then the remark is ‘F3’
    If the student is failed in 4 subjects then the remark is ‘F4’
    If the student is failed in 5 subjects then the remark is ‘F5’
    If the student is failed in 6 subjects then the remark is ‘F6’
    If the student is passed in all subjects then the remark is ‘P all’
    If the cells are blank then the cells in column ‘BE’ are blank.

    I want to give numbers to the students as per their merit in the class. The conditions are as follows:

    1. ‘P all’: 1, 2, 3,
    2. ‘F1’: continue sequence after ‘P all’ (for example: If there are 5 ‘P all’ students then the ‘F1’ number starts from 6 and so on till ‘F6’)
    3. The numbers should base on the total marks in column ‘AQ’. Only the sequence of
    numbering is as follows:
    P all, F1, F2, F3, F4, F5, F6

    Sorry, if the student is failed in 6 subjects then the remark is 'F6' in this case no marks are available in column. It contains 0. So the numbering should be as per their occurance in the result sheet.

    Please suggest me a code for it to achieve the target automatically.

    For output sample see attached file.

    Thanking you in anticipation.

    Regards,

    Mukesh
    Attached Files Attached Files
    Last edited by mukeshbaviskar; 04-06-2014 at 10:39 PM. Reason: Change attachment

  2. #2
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Give number as per students' merit automatically

    Please populate a sample data that would allow us to see the exact inputs. 20-50 rows would be more than enough, just make sure you have a row for every condition in there.

  3. #3
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Give number as per students' merit automatically

    Please see the attachment. I have shown a sample output there. If you have any question then ask me again. There are 11 rows with data.The data changes automatically by formulas.

    Thank you.

    Regards,

    Mukesh

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Give number as per students' merit automatically

    try this:
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Give number as per students' merit automatically

    Hi Jewal,
    Ok! Now it's sorting only column 'BE'. My requirement is to check the total marks in column 'AQ' and then give numbering as per sequence mentioned in my first post. I don't want to change the serial of the students. The result sheet should be as per their roll number. In short after filtering, giving numbers the data should be in its original sequence as shown in sample file attached.

    It requires multisort function for column 'BE' first and then column 'AQ' then give numbering in column 'BF'. Finally remove sorting and come to the original stage of the result sheet.

    Please do the modification to achieve the target.

    Thank you.

    Regards,

    Mukesh
    Last edited by mukeshbaviskar; 04-07-2014 at 12:56 AM.

  6. #6
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Give number as per students' merit automatically

    Mukesh,
    It seems, you don't really want to sort the rows - you just want to provide a 'Rank' under column BF? The Rank is based on 2-Levels:
    • Level 1: Based on the All Pass / F1 ... F6
    • Level 2: Based on Total ... Larger total means higher rank
    Is that an accurate understanding of your requirement?

  7. #7
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Give number as per students' merit automatically

    Hi Jewal,
    I have recorded a macro to achieve this target. Please go through it and clean up it and remove sort at the end and bring the resultsheet to its original stage as per the roll number of the students. After removing sorting the numbering should not be change. Only the result sheet should be as per roll number sequence of the students.

    The code is:

    Please Login or Register  to view this content.
    Thank you.

    Regards,

    Mukesh
    Last edited by mukeshbaviskar; 04-07-2014 at 01:37 AM.

  8. #8
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Lightbulb Re: Give number as per students' merit automatically

    If you are just looking to Rank the scores; here's a simple approach using formula.
    Merit numbering.xlsm. (Refer to last two columns)

  9. #9
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Give number as per students' merit automatically

    Hi Jewel,
    Excellent! It's a nice approach to achieve the target with a single formula. In this case we have to create a etra remark column and enter the numbers manually which I want to do automatically. I think we may achieve it by substitute formula which can be hidden column in the result sheet.

    Thank you.

    Regards,

    Mukesh

  10. #10
    Valued Forum Contributor
    Join Date
    12-02-2012
    Location
    Melbourne, VIC
    MS-Off Ver
    Excel 2016
    Posts
    750

    Re: Give number as per students' merit automatically

    Happy to help. Kindly mark the thread as solved.
    Cheers!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Average Function Limited to the Number of Students
    By alyaahmed in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-06-2014, 10:49 AM
  2. [SOLVED] Count number of students depanding on their score.
    By moxeve in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-17-2013, 06:01 AM
  3. [SOLVED] Remove the Absent Students from the List of Failing Students
    By alyaahmed in forum Excel General
    Replies: 2
    Last Post: 03-28-2013, 01:40 PM
  4. Replies: 11
    Last Post: 03-21-2013, 11:03 AM
  5. Payments merit
    By tofimoon4 in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 07-21-2009, 06:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1