Ranking system

    I am trying to create a spreadsheet where a ranking system can be inserted and updated.


    I have one worksheet where people are ranked. mark 1, paul 2, etc and goes down like that.
    The next worksheet has a list of "sections" where people would go in ranking order. However, not everyone on the ranking page will be on the sections sheet. I want to be able to put someone like Paul on the sections sheet and he will be placed in ranking order. for example i have paul 2, jim 4, suzy 7. I want the sections sheet to put them in ranking order. How can i do this? The ranking worksheet has one cell for the name and a seperate cell for the ranking order.


    thanx!

    If you put (Paul 2) into the appropriate column with existing data on the "Sections" Sheet, can't you just sort by the ranks to get them into the correct order? (Or better yet, upon adding the name, could you have a macro that automatically sorts the list?)


    Otherwise, if you want a more complex solution in which you choose all of the people you want put into the sections ws in order, I'd probably consider a listbox on a UserForm.


    If you need help with either the sorting macro or developing a UserForm, post back and we can help (possibly uploading a small sample of your workbook so we can see the format you have/want).

    I uploaded a sample of my workbook. it's just to figure out what to do then i will format it.


    Rankings are automatically entered at the beginning of the month. I want the "17 chart" sheet to automatically read the rankings from the rankings sheet and put the names in descending order (by rank basically) on the "people on shift" sheet. Then i want the "17 chart" sheet to pull from the "people on shift" sheet and arrange them in the according stations automatically. so everything on the "17 chart" should be automated so the user can not change anything. Does that make sense? thanx for your help!
    please let me know what you think would be the best way to do this.

    Awesome!
    that's exactly what i wanted! I can fix the third sheet myself by just setting it to grab the first from the list, the second, etc.
    I have to go to work but i will work on it tomorrow and let you know how it works out. thank you very much!

    ok I'm confused on your formulas
    I can't figure out how to manipulate them so i can put them wherever i want.
    can you explain them to me? if possible.

    1) I used column L in "People in Shift" sheet as working column to find rank if cells in column A has value
    [INDENT]e.g. If A8 is Brenda, L8 finds Brenda's rank from Ranking Sheet which is 5.
    Each cell up to L23 does the same for corresponding A value.
    And I made the column invisible setting font colour with white.[/INDENT]


    2) Cells in Column F then find the smallest value within column L and display corresponding value in column G in Ranking sheet.
    Actually, I'd like to cahnge the formula for column F as shown below
    [INDENT]Cell F8:
    =IF(A8="","",INDEX(Ranking!G:I,MATCH(SMALL(L:L,ROW(A1)),Ranking!I:I,0),1))
    Then just Enter instead of Ctrl+Shift+Enter.
    SMALL(L:L,ROW(A1))
    ROW(A1) is the substitute of 1 and it increases as you drag down, therefore if you go next row, it will be the second smallest and so forth.
    ROW(A1), therefore, nothing to do with actual cell A1.[/INDENT]


    Does it help you?


    Jindon

    Yes! it works, thank you very much but how did you do it? i noticed some new formulas in column C on the "totals" sheet. Why did you need these?
    and can you explain them? thanx!

    Hi,
    Here's how it works.


    Formula in col.C on Totals Sheet is to find the rank among "EVAL TOTALS", col.B
    and I thought this would be the original ranking.
    =IF(B2<>"",RANK(B2,B:B),"")
    One problem!
    Function RANK will rank the same if the test value is the same.
    When it happens, function SMALL will always return the first comer only.
    To avoid this problem, I always use another working column which adds or subtracts ROW(A1)/100 to or from the test value.
    It really avoids this problem. (The attached has been modified already, ref col.G and formula in co.C is now =IF(B2<>"",RANK(G2,G:G),"") )


    Formula in col.L on sheet Shift Input
    =IF(ISERROR(MATCH(C10,TOTALS!A:A,0)),"",INDEX(TOTALS!A:C,MATCH(C10,TOTALS!A:A,0),3))
    It finds the original ranking of the value in col.C from Totals sheet


    Formula in Col.F on sheet Shit Input
    =IF(C10="","",INDEX(TOTALS!A:C,MATCH(SMALL(L:L,ROW(A1)),TOTALS!C:C,0),1))
    It finds the first smallest value in col.L for the first row and find ‚ƒ‚‚’‚’‚…‚“‚‚‚Ž‚„‚‰‚Žg value in col.A on Totals sheet and second smallest for the second row and so forth.


    Try the attached and compare with the other one

    I didn't think about some people having the same rankings
    Thank you for all your help. I am slowly learning excel and still understanding the formula you have given me slowly.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login