Hi,
I have a school class with data from a recent test.
The test was out of 10.
Some students scored the same mark as others.
I'd like to display the score (from 10 down to 1) in the first column, and then list all the students who attained each score in the row next to their score.
My spreadsheet has the data in a small table, next to it is my attempt at using XLOOKUP, but it only returns one of the values, not duplicates. The third table is what I'd like my output to look like
I know that I can use wildcards, look from the bottom up, etc. with XLOOKUP, but I don't know how to make it give me the different students who scored the same score as each other. Pupils with the same score can be written in adjacent cells or all be written in the same cell, I don't mind. I can always CONCATENATE them after.
The reason is that we normally hand write a paper chart with all the score values in the left-hand column (from 1 up to the maximum test score) and then simply fill in by hand the names of each student in the row corresponding to their score. This allows a visual overview of the group's performance AND we can put several groups side-by-side to see how different groups fared compared with each other (spread and distribution of scores).
Any guidance gratefully accepted.
Thanks in advance.
Nibbos
PS In my final version I may have up to 100 students, so there may be 5-10 students who get the same score at any one time.
I have a school class with data from a recent test.
The test was out of 10.
Some students scored the same mark as others.
I'd like to display the score (from 10 down to 1) in the first column, and then list all the students who attained each score in the row next to their score.
My spreadsheet has the data in a small table, next to it is my attempt at using XLOOKUP, but it only returns one of the values, not duplicates. The third table is what I'd like my output to look like
I know that I can use wildcards, look from the bottom up, etc. with XLOOKUP, but I don't know how to make it give me the different students who scored the same score as each other. Pupils with the same score can be written in adjacent cells or all be written in the same cell, I don't mind. I can always CONCATENATE them after.
The reason is that we normally hand write a paper chart with all the score values in the left-hand column (from 1 up to the maximum test score) and then simply fill in by hand the names of each student in the row corresponding to their score. This allows a visual overview of the group's performance AND we can put several groups side-by-side to see how different groups fared compared with each other (spread and distribution of scores).
Any guidance gratefully accepted.
Thanks in advance.
Nibbos
PS In my final version I may have up to 100 students, so there may be 5-10 students who get the same score at any one time.
| Test scores results viewer.xlsx | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | |||
| 1 | Test data | Analysis | Analysis | ||||||||||||
| 2 | student | score | Score | Student | Student | Student | Score | Student | Student | Student | |||||
| 3 | alan | 6 | 10 | 10 | |||||||||||
| 4 | ben | 2 | 9 | denise | 9 | denise | |||||||||
| 5 | chris | 8 | 8 | chris | 8 | chris | kate | ||||||||
| 6 | denise | 9 | 7 | hilda | 7 | hilda | |||||||||
| 7 | enid | 6 | 6 | alan | 6 | alan | enid | geo | |||||||
| 8 | flo | 4 | 5 | ian | 5 | ian | jack | ||||||||
| 9 | geo | 6 | 4 | flo | 4 | flo | |||||||||
| 10 | hilda | 7 | 3 | 3 | |||||||||||
| 11 | ian | 5 | 2 | ben | 2 | ben | |||||||||
| 12 | jack | 5 | 1 | 1 | |||||||||||
| 13 | kate | 8 | 0 | 0 | |||||||||||
| 14 | |||||||||||||||
| 15 | what I have achieved so far | what I want it to look like | |||||||||||||
| 16 | |||||||||||||||
Sheet1 | |||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E3:E13,J3:J13 | E3 | =XLOOKUP($D3,Table1[score],Table1[student],"") |
