0

I have a list of names with an average beside it for every day Monday-Friday. A subgroup of names within this list will be highlighted. I want a macro to assign ranks of the highlighted individuals based on their average and place a value of their rank in a row of another sheet where each individual has a column. There is one row for each day.

Example. 5 people highlighted in a list of July 1st. Macro looks at other sheet (called Workbook) and finds row corresponding to July 1st. Then finds column corresponding to person with lowest average. Puts 1 in their cell. Repeats this with 2, 3, 4, 5. If 8 people highlighted would do this 1 through 8.

Link to a copy of the sheet https://docs.google.com/spreadsheets/d/1DZif3MWw4xnyrK54OSm-htIty4G9XwMNZstROzaFk94/edit?usp=sharing

hope this makes sense

Improve this question
2
  • pls share a copy of your sheet
    – user0
    Jun 13, 2019 at 10:56
  • added link. thank you.
    – Lance
    Jun 13, 2019 at 15:27

1 Answer 1

Reset to default
0

  • paste in P8 cell, then select P8 press CTRL + C and paste in S8, V8, Y8, etc.

    =ARRAYFORMULA(IFERROR(SORT(TRANSPOSE({'Workbook '!$E2:$P2; RANK(INDIRECT("Workbook !"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 5, 4)&":"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 16, 4))*1, INDIRECT("Workbook !"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 5, 4)&":"&
 ADDRESS(MATCH(P5, 'Workbook '!$D:$D, 0), 16, 4))*1)}), 2, 1)))

enter image description here

Improve this answer
2
  • I appreciate this reply. Can you walk me though how this works? I'm struggling to follow the logic. Thanks.
    – Lance
    Jun 13, 2019 at 18:35
  • @LanceSalmikivi sure, with MATCH we get number of row where P5 is equal to row from D:D range. this number is then inserted into ADDRESS formula so we get range E4 then with INDIRECT we create a range E4:P4. this is then inserted into RANK formula which assigns a rank to each number in range E4:P4 (if E4:P4 is empty you will get value error so to counter that we multyply it wit 1 so if the range is empty after multiplication its 0) then this ranked row is combined with names E2:P2 in array brackets {}. next the TRANSPOSE turns rows into columns and SORT sorts both columns based on 2nd column
    – user0
    Jun 13, 2019 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.