How to find the most frequent combination in a dataset.

[winds]

Hello. I have a dataset that goes something like this

	text	type	change	character	value	reason
Alfred	a	b		a	d
Betty	a	c	a	c	g	s
Charlie		v	v	v	g	d

The letters in the columns do not matter (it's actually numbers, I'm just altering the data for privacy purposes). But basically, I want to know in this data set, which is the most common combination. In this simple example, the highest combination can be taken from type, value and character where they occur three times. Second highest would be reason and change, and the least would be text.

The actual data set is much larger hence the need for some.. method.

 

[Cubist]

What 2nd example?
Why would there be two frequency lines of 2?
If there are no headers with the frequency of 5 why would it be there?
How do you determine what goes in that Additional column?

You're posting in pieces. Please give a complete data sample reflective of your actual data and the expected output from that in one post.

 

[winds]

What 2nd example?
Why would there be two frequency lines of 2?
If there are no headers with the frequency of 5 why would it be there?
How do you determine what goes in that Additional column?

You're posting in pieces. Please give a complete data sample reflective of your actual data and the expected output from that in one post.

My apologies. I made a bit amendment to the original data which was at end of page 1 of this thread

 

[Cubist]

Is this what you mean?
I don't see how the a,c,v frequencies have to do with the frequency of the headers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		text	type	change	character	value	reason
2	Alfred	a	a		a	a			3	type, character, value		6	c
3	Betty	c	c	c	c	c	c		2	text, change, reason		5	v
4	Charlie		v	v	v	v	v					4	a
5
6	Count	2	3	2	3	3	2
7
Sheet11

Cell Formulas
Range	Formula
I2:I3	I2	=UNIQUE(LARGE($B$6#,SEQUENCE(COLUMNS(B1:G1))))
J2:J3	J2	=BYROW(IF(I2#=B6#,B1:G1,""),LAMBDA(r,TEXTJOIN(", ",TRUE,r)))
L2:M4	L2	=LET(a,UNIQUE(TOCOL(B2:G4,3)),b,BYROW(a,LAMBDA(r,COUNTIF(B2:G4,r))),SORTBY(HSTACK(b,a),b,-1))
B6:G6	B6	=BYCOL(B2:G4,LAMBDA(col,COUNTA(col)))
Dynamic array formulas.

 

[winds]

Y

Is this what you mean?
I don't see how the a,c,v frequencies have to do with the frequency of the headers.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		text	type	change	character	value	reason
2	Alfred	a	a		a	a			3	type, character, value		6	c
3	Betty	c	c	c	c	c	c		2	text, change, reason		5	v
4	Charlie		v	v	v	v	v					4	a
5
6	Count	2	3	2	3	3	2
7
Sheet11

Cell Formulas
Range	Formula
I2:I3	I2	=UNIQUE(LARGE($B$6#,SEQUENCE(COLUMNS(B1:G1))))
J2:J3	J2	=BYROW(IF(I2#=B6#,B1:G1,""),LAMBDA(r,TEXTJOIN(", ",TRUE,r)))
L2:M4	L2	=LET(a,UNIQUE(TOCOL(B2:G4,3)),b,BYROW(a,LAMBDA(r,COUNTIF(B2:G4,r))),SORTBY(HSTACK(b,a),b,-1))
B6:G6	B6	=BYCOL(B2:G4,LAMBDA(col,COUNTA(col)))
Dynamic array formulas.

Yes something like that, but if it's possible so that it can figure out which one most frequent combination.

Example acv occus 3x, as well as cv occus 5x

 

[Cubist]

I don't think it's possible to do that with formulas and must use VBA instead. Secondly, the maximum unique value (a,b,c,v, etc...) you can have is 20 before it reaches the last row of Excel. I think you're understating the problem with your example. I don't think I can assist further here. Post your question in a new thread. Clearly show what you want and provide better sample data that reflects your actual data.