Most frequently occurring text in array formula

[jdmc45]

Hi all,

I am trying to create a formula which finds the most frequently occurring text value (all entries all text) in an areay

I am using INDEX(D4:D7,MODE.SNGL(MATCH(D4:D7,D4:D7,0))) but this only works for single columns or single rows not a 3x3 or ZxZ array for example

Can someone please help?

 

[Cubist]

Which version of Excel are you using?

 

[jdmc45]

Microsoft 365 MSO version 2401 64 bit

 

[Cubist]

You can turn it into a single column using TOCOL()

Book1
	A	B	C	D	E	F	G
1	E	S	Z	D	B		B
2	W	R	V	V	B
3	X	Y	H	P	U
4	F	T	U	Q	Z
5	N	B	U	V	Y
Sheet2

Cell Formulas
Range	Formula
G1	G1	=LET(t,TOCOL(A1:E5,3),INDEX(t,MODE.SNGL(MATCH(t,t,0))))

 

[Cubist]

Use MODE.MULT if there are tie-breakers.

=LET(t,TOCOL(A1:E5,3),INDEX(t,MODE.MULT(MATCH(t,t,0))))

 

[Fluff]

Microsoft 365 MSO version 2401 64 bit

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’)

 

[jdmc45]

You can turn it into a single column using TOCOL()

Book1
	A	B	C	D	E	F	G
1	E	S	Z	D	B		B
2	W	R	V	V	B
3	X	Y	H	P	U
4	F	T	U	Q	Z
5	N	B	U	V	Y
Sheet2

Cell Formulas
Range	Formula
G1	G1	=LET(t,TOCOL(A1:E5,3),INDEX(t,MODE.SNGL(MATCH(t,t,0))))

Thanks - this worked well.

How would I go about finding the second and third most common test value here please ? Extending the above

 

[Cubist]

Maybe this. You can change the n-th largest in cell H1.

Book1
	A	B	C	D	E	F	G	H
1	E	S	Z	D	B		n-th largest	2
2	W	R	V	V	B			V
3	X	Y	H	P	U
4	F	T	U	Q	Z
5	N	B	U	V	Y
Sheet1

Cell Formulas
Range	Formula
H2	H2	=LET(d,TOCOL(A1:E5),f,BYROW(UNIQUE(d),LAMBDA(br,ROWS(FILTER(d,br=d)))), CHOOSEROWS(SORTBY(UNIQUE(d),f,-1),H1))