Get the array of "students" having as maximum score of X

[Solvap]

Hello Everyone,

I hope you can help me on this one which I thought to be easy, but not anymore
I short, I have a list of students who are associated with scores (more than 1 and some can be equal). I'm looking for a way to get an array of the students (that I will concatanate with TEXTJOIN) that have as maximum score a value that I have specified in another cell.

Example:

StudentID	Score
A	3
C	1
B	4
A	2
C	3
D	5
A	3
B	2
A	2
D	1
C	3
C	1

List of students having a maximum score of : 3
Result: A, C

As you can see in the table, students A and C have a maximum score equal to 3.

So far, I've been able to do that by using an extra column showing that maximum score per StudentID but I'm looking for a solution without extra column.

Thank you in advance for your time.

Solvap

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version of xl, maybe

++Fluff.xlsm
	A	B	C	D
1	StudentID	Score
2	A	3		3
3	C	1		A, C
4	B	4
5	A	2
6	C	3
7	D	5
8	A	3
9	B	2
10	A	2
11	D	1
12	C	3
13	C	1
Main

Cell Formulas
Range	Formula
D3	D3	=LET(u,UNIQUE(A2:A13),TEXTJOIN(", ",,IF(MAXIFS(B2:B13,A2:A13,u)=D2,u,"")))

 

[Solvap]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Depending on your version of xl, maybe

++Fluff.xlsm
	A	B	C	D
1	StudentID	Score
2	A	3		3
3	C	1		A, C
4	B	4
5	A	2
6	C	3
7	D	5
8	A	3
9	B	2
10	A	2
11	D	1
12	C	3
13	C	1
Main

Cell Formulas
Range	Formula
D3	D3	=LET(u,UNIQUE(A2:A13),TEXTJOIN(", ",,IF(MAXIFS(B2:B13,A2:A13,u)=D2,u,"")))

Hello @Fluff,

Thanks a lot for your reply, it works perfectly ! BTW, thank you also for the profile info, I've just updated it

Have a nice evening.
Solvap

 

[Fluff]

You're welcome & thanks for the feedback.
Thanks also for updating your profile.