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]

Do you count duplicates as 1 or 2? For example b, c, v, v is 4 or 3?

 

[winds]

Do you count duplicates as 1 or 2? For example b, c, v, v is 4 or 3?

Duplicates doesn't matter.. they could all be the same values. What's important is the number of occurences in the column and row.

 

[Cubist]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		text	type	change	character	value	reason		Frequency	Headers
2	Alfred	a	b		a	d			3	type, character, value
3	Betty	a	c	a	c	g	s		2	text, change, reason
4	Charlie		v	v	v	g	d
5
6	Count	2	3	2	3	3	2
7
Sheet9

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)))
B6:G6	B6	=BYCOL(B2:G4,LAMBDA(col,COUNTA(col)))
Dynamic array formulas.

 

[winds]

How about this?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		text	type	change	character	value	reason		Frequency	Headers
2	Alfred	a	b		a	d			3	type, character, value
3	Betty	a	c	a	c	g	s		2	text, change, reason
4	Charlie		v	v	v	g	d
5
6	Count	2	3	2	3	3	2
7
Sheet9

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)))
B6:G6	B6	=BYCOL(B2:G4,LAMBDA(col,COUNTA(col)))
Dynamic array formulas.

Your method looks like it works and correct. However I can't seem to apply it my data set. I got the #NAME error for formula in B6. Also the popup that shows problem in formula for cellI2..

 

[Cubist]

Which version of excel are you using 365 or 2019?

 

[winds]

Which version of excel are you using 365 or 2019?

2019

 

[Cubist]

2019

The one I provided only works in 365. I'll have to think about 2019.

 

[winds]

Hello. I have a dataset that goes something like this

	text	type	change	character	value	reason
Alfred	a	a		a	a
Charlie	c	c	c	c	c	c
Veronica		v	v	v	v	v

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.

I have edited for the data for another question. So again we have two cases that have solved where type and characted and value occurs the most.

However is it also possible to find the most occurence regardless of the other data? What I mean here is for example, c and V occurs the most here, 5 times.

 

[Cubist]

With out version 365 dynamic functions. I don't see an easy way to accomplish what you're asking for. Maybe VBA is an option. Not sure I can assist further.