1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

want a code or formula to create merit list

Discussion in 'Business Applications' started by toofani, Dec 20, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Dear All

    I am hoping to get helped with a macro or a formula which can select best students for limited seats

    for example for a course of "ch" i have 4 seats, i want to select best students who can have "ch" based on their score. then i will go for other course "el" and same for all

    i have included a dummy data for information, and wanted result on another sheet. if possible different sheets for different courses can be made


    For the students who are assigned to specific course, should not be included in other courses.


    Its kind of a merit list for different courses.

    Please help
     

    Attached Files:

  2. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Hans are you listening bro :)
     
  3. Promor

    Promor

    Joined:
    Aug 13, 2015
    Messages:
    12
    There are a number of ways this could be done,
    The most basic but time consuming to set up is to use a simple vlookup formula, but you could also use a more powerful array formula which I’ve also included an example of.
    With the Array Formula if you change the no of seats in each category, the tables on the wanted results automatically update. I’ve set it up so you could have up to 10 seats for each class.
    When entering or editing an array formula you need to press Ctrl+****+Enter at the same time to get the { } to appear around the formula.
     

    Attached Files:

  4. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Thank you very much for an effective response, i have some questions here, if you can answer, i will be so glad

    first can i extend these seats to atleast 50 or any other number. and can you explain what formula does. I need it like if i have 100 persons who have selected a particular course, but i have only 25 seats for that. The formula should do that first assign 25 on the basis of score, then assign the remaining 25 based on their second choice, and still we will remain the next course which will be assigned based on their third choice. Here is the tricky thing. I have to decide the courses specifically, i mean first i have to decide one course . then other then other .. so i would request another workout.

    i give. seats limit and the short code of the program in two cells

    the formula should automatically select the first 25 for that program, and puts them out from the data and paste it on the other sheet somewhere by their name.

    now remaining first choice for the worked out program should change and replaced by second choices. now i again type the next program and seats limit, and the formula / macro again pick best students and save it on another sheet.

    the remaining students third choice, and one who have selected the program as first choice their second choice replaces with their next choice..

    its complicated pardon for my bad english.. I will be very glad to see any macro for this
     
  5. Promor

    Promor

    Joined:
    Aug 13, 2015
    Messages:
    12
    Wow lots of questions. I’ve got a few for you but we’ll get to those later.

    Yes the formula can be expanded to any size you like, it is controlled on the result sheet by the number of rows the formula is copied to the number of seats you show for each course on sheet 1. I have some data sets that run to 10000+ rows where I use this type of formula.

    As it is the sheet will work for upto 10 seats on each course.

    The formula looks quite tricky but to break it down its not too bad.

    =IFERROR(INDEX(Sheet1!$A$2:$M$16,SMALL(IF(Sheet1!$A$2:$A$16<=Sheet1!$P$2,ROW(Sheet1!$A$2:$A$16)),ROW(1:1))-1,2),"")

    IFERROR if the formula does not find a valid match it leaves the cell blank.
    INDEX(Sheet1!$A$2:$M$16 this is setting the range where the data is
    SMALL(IF(Sheet1!$A$2:$A$16<=Sheet1!$P$2 when combined with ROW(1:1) This finds the first row on the sheet where the rank is less than or equal to the number of seats available. If it was ROW(2:2) it would find the second row and so on.
    -1,2 The first match is in row 2 of the sheet, but that is row 1 of the data range so we have to subtract 1 from the number of rows to tell excel we want the first row of the data range. The number 2 indicates we want the data from the second column of the data range.
    The link below gives a good description of how to build array formulas.
    http://fiveminutelessons.com/learn-...ple-values-list#sthash.71n4zXyL.dAN5aRZN.dpbs
    Moving on to how you really want the sheet to work as I understand it The highest scoring students get their first choice course to fill the available seats.
    Then the next group of students get their second choice course based on their score. Am I right in thinking that a higher score and second choice course would get a seat before a lower score and a first choice ?
    The when the second course is full it goes to the third choice and so on.
    On the sheet you have 9 possible choices, is that total number of courses?
    What would happen if all the seats on a course were not taken from everyone’s first choice? Would you leave the course part filled ? or would you put in Students who selected the course as their second choice or would they get their first choice course if it was available?

    Am I correct in assuming your data will always be ranked by score?

    This build could turn out to be quite complex, however I do have a couple of ideas.

    Finally, I stick to formulas because I’m rubbish at writing macros !
     
  6. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Thank you very much for your kind response.

    Yes it is indeed helpful to decide. however, could i break the work in portions. first i would like to answer ur questions

    yes the deciding factor is score. It is a merit list assigning different programs on the basis of choices and limitations of the institute seats.
    so we offer student to select atleast 9 choice we have 9 programs by the way. 1000 students apply... some programs have big numbers some have small. we have to decide first the big numbers then go for smaller one. if seats remain empty then we again plan our admision interviews to fill those seats, now here is another point to be put on. We have finalized 25 or 30 students on respective seats, but sometimes 20 students join and pay the fees, then we have to again recheck the merit, and the student which was first offered 2nd choice due to merit competition now be eligible for the seat which remained empty.

    Thats why i was looking to have a macro for that so that.. it is complicated but very informative and helpful for others in many ways.

    Thank you for your kind support, i hope i could see some other useful tips for the above mentioned scinario
     
  7. Promor

    Promor

    Joined:
    Aug 13, 2015
    Messages:
    12
    Ok so there are a couple more things to incorporate thank you for the clarification.
    I'm about to go on holiday so it may be a while before I can look at this again.
    One other point with Array formulas is they will automatically update when the data changes so if someone does not pay the allocation of seats would automatically update.
     
  8. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    i applied the formula, which is bigger than the dummy, what i observe it hangs and processes late.. is there any solution to this. thats where we like to have macro instead of array formula which slow to work
     
  9. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    Dear Promor

    I am still waiting for you, stuck in the middle of setting formula of 9 choices.
     
  10. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    i have just observed this that its just considering the score, actually what we need is that

    it should check first the first choice of the candidate then check the availability of seat, if there then place him / her in it, if seats of certain program is filled, then it should go to second, third or fourth till ninth choice respectively, if till 8th program seats are filled.

    The seat allocation will be on score + choice.. Please help.. this formula is just based on score and row no. which is not a correct choice.
     
  11. toofani

    toofani Thread Starter

    Joined:
    Jun 15, 2012
    Messages:
    36
    can any body else could help on this, that would be so great
     
  12. Promor

    Promor

    Joined:
    Aug 13, 2015
    Messages:
    12
    I'm just back from holiday, and as you said you didn't want / like an array formula solution I stopped working on this.....
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1162614

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice