Find largest value in data but also return result if the largest value appears twice

[Sifuevs]

Hi everyone,
I have an Excel sheet that scores investigation outcomes based on criteria met by a series of questions.
It then looks at the highest score and returns an assumption.
My issue is that if 2 different questions score 10 (or the same score) then it will only return one assumption.
I would like a formula so i can display both assumptions in separate columns. Please see below.
I am a relative novice at excel so would like to keep the structure of sheet and not change everything. I understand that my formula is probably not the most efficient way.
In advance thank you to everyone who provides input.
Have a wonderful day

 

[arthurbr]

Why not filter the column containing the score?

 

[Sifuevs]

Why not filter the column containing the score?

Hi Arthur,
thanks for the reply.
The sheet is being used by staff who have no Excel experience.
So cell BE returns the highest result code and BF returns the best assumption based on the code in BE. I would like to have another column that would return the secondary best assumption.
So as M has scored 10 and I can get it to display the assumption based on the score i would like also to display the O assumption result as this has also scored 10.
In an ideal world if two results score 10 display both assumptions in separate cells otherwise display the highest assumption first and then look for the next highest scoring reason code and display that assumption in a separate cell.
Hope all this makes sense lol.

Thank you

 

[Cubist]

You didn't leave any column for a tie-breaker. Where would you want it to be? Can there be more than 2 "best"?
Can you show an expected output.

 

[Sifuevs]

You didn't leave any column for a tie-breaker. Where would you want it to be? Can there be more than 2 "best"?
Can you show an expected output.

Hi Cubist,
thanks for the reply. I think maybe the tie breaker in BG and then alternate assumptions in BH and so on. Also, yes there may be a possibility that there may be more than 2 "best" or at least 2 scores the same.
Appreciate you my friend, thank you.

 

[Cubist]

This shows the ranking in descending order. Did you want to call out tie-breakers or having a descending order list from highest to lowest is ok.

Book3
	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
4	M	M COUNT	I	I COUNT	L	L COUNT	O	O COUNT	R	R COUNT	K	K COUNT	A1	A1 COUNT	A2	A2 COUNT	BEST	Reasoning	Alternatives
5	10	M	0		6	L	10	O	0		2	K	0		0		M		O	L	K
6			0		0		0		0		0		0		0
7			0		0		0		0		0		0		0
Sheet1

Cell Formulas
Range	Formula
BE5	BE5	=TAKE(CHOOSECOLS(SORT(WRAPROWS(AO5:BD5,2,""),1,-1),2),1)
BG5:BI5	BG5	=TOROW(DROP(CHOOSECOLS(SORT(WRAPROWS(AO5:BD5,2,""),1,-1),2),1),3)
Dynamic array formulas.

 

[Sifuevs]

This shows the ranking in descending order. Did you want to call out tie-breakers or having a descending order list from highest to lowest is ok.

Book3
	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
4	M	M COUNT	I	I COUNT	L	L COUNT	O	O COUNT	R	R COUNT	K	K COUNT	A1	A1 COUNT	A2	A2 COUNT	BEST	Reasoning	Alternatives
5	10	M	0		6	L	10	O	0		2	K	0		0		M		O	L	K
6			0		0		0		0		0		0		0
7			0		0		0		0		0		0		0
Sheet1

Cell Formulas
Range	Formula
BE5	BE5	=TAKE(CHOOSECOLS(SORT(WRAPROWS(AO5:BD5,2,""),1,-1),2),1)
BG5:BI5	BG5	=TOROW(DROP(CHOOSECOLS(SORT(WRAPROWS(AO5:BD5,2,""),1,-1),2),1),3)
Dynamic array formulas.

Thanks Cubist.
Yes, maybe it would be a good idea to call out the tie-breakers. Thanks

 

[Sifuevs]

Hi Cubist,
I have pasted into BE5 and I get this message.

 

[Cubist]

In a blank cell, what happens when you type =SORT or =SORTBY. Do they poppulate?
Are you using version 365 of Excel?