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

  • pls share a copy of your sheet – user0 Jun 13 '19 at 10:56
  • added link. thank you. – Lance Jun 13 '19 at 15:27
  • paste in P8 cell, then select P8 press CTRL + C and paste in S8, V8, Y8, etc.

     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

  • I appreciate this reply. Can you walk me though how this works? I'm struggling to follow the logic. Thanks. – Lance Jun 13 '19 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 '19 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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