I'm trying to match the 3 highest and 3 lowest sections of a test scored in excel automatically (17 sections total). The grades of each section are printed out between the sections, so there may be spaces within the same column that should be ignored (image below). I'm trying to match the highest, 2nd highest, and 3rd highest scoring sections from the list without duplicates.
Right now because multiple sections may have a score of 100%, if there are two sections with 100%, match will duplicate the first result, instead of the next unique result. If there are multiple 100s or same scores, it is ok to rank top down (1st found / etc.). There's also an issue with merged cells @ the output because of spacing (image below).
Any help appreciated. Sincerely thank you!
Testing Formulas (for table below):
=INDEX(D6:D33,MATCH(LARGE(E6:E33,1),E6:E33,0))
=INDEX(D6:D33,MATCH(LARGE(E6:E33,2),E6:E33,0))
=INDEX(D6:D33,MATCH(LARGE(E6:E33,3),E6:E33,0))
=INDEX(D6:D33,MATCH(SMALL(E6:E33,1),E6:E33,0))
=INDEX(D6:D33,MATCH(SMALL(E6:E33,2),E6:E33,0))
=INDEX(D6:D33,MATCH(SMALL(E6:E33,3),E6:E33,0))
Actual Example Formula (from doc)
=INDEX($B$47:$B$558,MATCH(LARGE($N$47:$N$558,1),$N$47:$N$558,0))
Other Formulas Tried
=LET(rng,CHOOSE({1,2},B47,B85,B141,B163,B187,B207,B231,B262,B283,B308,B327,B353,B379,B413,B437,B465,B500,N47,N85,N141,N163,N187,N207,N231,N262,N283,N308,N327,N353,N379,N413,N437,N465,N500),rws,ROWS(rng),INDEX(SORT(rng,2,-1),CHOOSE({1;2;3;4;5;6},1,2,3,rws,rws 1,rws 2,1)))
=LET(rng,CHOOSE({1,2},D6:D33,E6:E33),rws,ROWS(rng),INDEX(SORT(rng,2,-1),CHOOSE({1;2;3;4;5;6},1,2,3,rws,rws-1,rws-2,1)))
Testing Data (real data would be blank, but I needed the '-' for formatting here)
| Description | Score (%) |
|---|---|
| Section 1 | 100 |
| - | - |
| - | - |
| - | - |
| Section 2 | 30 |
| - | - |
| Section 3 | 102 |
| Section 4 | 83 |
| - | - |
| - | - |
| Section 5 | 85.5 |
| Section 6 | 97.7 |
| - | - |
| - | - |
| - | - |
| - | - |
| Section 7 | 90 |
| Section 8 | 100 |
| Section 9 | 100 |
| Section 10 | 98 |
| Section 11 | 99 |
| - | - |
| - | - |
| - | - |
| Section 12 | 75 |
| Section 13 | 75 |
| Section 14 | 80 |
| Section 15 | 37 |
| Answers | - |
|---|---|
| Highest | Section 3 |
| 2nd Highest | Section 1 |
| 3rd Highest | Section 1 |
| Lowest | Section 2 |
| 2nd Lowest | Section 15 |
| 3rd Lowest | Section 12 |
| Desired Answers | - |
|---|---|
| Highest | Section 3 |
| 2nd Highest | Section 1 |
| 3rd Highest | Section 8 |
| Lowest | Section 2 |
| 2nd Lowest | Section 15 |
| 3rd Lowest | Section 12 |
CodePudding user response：
Add Filter to remove the non-numeric rows:
=LET(rngprm,CHOOSE({1,2},D6:D33,E6:E33),rng,FILTER(rngprm,ISNUMBER(INDEX(rngprm,0,2))),rws,ROWS(rng),INDEX(SORT(rng,2,-1),CHOOSE({1;2;3;4;5;6},1,2,3,rws,rws-1,rws-2,1)))
Edit:
If they were truly blank and not - or a formula that returns "" blank then:
=LET(rngprm,CHOOSE({1,2},D6:D33,E6:E33),rng,FILTER(rngprm,INDEX(rngprm,0,1)<>0),rws,ROWS(rng),INDEX(SORT(rng,2,-1),CHOOSE({1;2;3;4;5;6},1,2,3,rws,rws-1,rws-2,1)))