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]

Here's a semi-manual way of the first question. I would post your second question in a new thread. Make sure you indicate version 2019 as you have both listed on your profile.
x
Step 1: Find the count of each column.

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

Cell Formulas
Range	Formula
B6:G6	B6	=COUNTA(B2:B4)

Step 2: Paste the column headers and the count as values in a table format.

Book1
	I	J
1	Header	Count
2	type	2
3	character	3
4	value	2
5	text	3
6	change	3
7	reason	2
Sheet9

Step 3: Highlight the table in step 2 -> Data Tab -> Sort ->

Final Result:

Book1
	L	M
1	Header	Count
2	type	3
3	character	3
4	value	3
5	text	2
6	change	2
7	reason	2
Sheet9

 

[winds]

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.

Hi. I've transferred to my 365 version. Unfortunately I'm getting #REF under the Frequency and Header Columns. For COUNT it only displays the formula next to it. I'm getting the formula in this cell contains an error for the COUNT area.

 

[Cubist]

You're probably not referencing the ranges correctly. I can't diagnosis without seeing what you entered.

 

[winds]

You're probably not referencing the ranges correctly. I can't diagnosis without seeing what you entered.

0​

0​

0​

0​

0​

82​

5​

13​

0​

0​

0​

100​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

21​

0​

0​

21​

42​

14​

0​

28​

0​

42​

0​

11​

0​

0​

0​

11​

0​

10​

41​

51​

17​

0​

38​

26​

24​

0​

14​

119​

58​

0​

10​

0​

22​

0​

16​

17​

123​

0​

27​

0​

22​

0​

49​

0​

0​

17​

0​

17​

0​

16​

35​

0​

35​

86​

8​

0​

17​

25​

0​

30​

17​

23​

14​

39​

20​

143​

0​

0​

21​

0​

19​

0​

0​

17​

57​

13​

25​

8​

0​

19​

65​

0​

0​

10​

20​

30​

0​

3​

0​

12​

14​

29​

18​

57​

17​

92​

25​

31​

106​

36​

101​

69​

45​

413​

22​

29​

39​

37​

77​

49​

46​

56​

355​

44​

51​

29​

61​

82​

267​

45​

46​

56​

38​

185​

27​

29​

35​

24​

26​

141​

14​

30​

22​

66​

15​

46​

9​

37​

21​

91​

43​

262​

0​

21​

8​

26​

25​

27​

9​

17​

133​

7​

17​

29​

48​

32​

133​

16​

22​

15​

32​

85​

24​

61​

38​

36​

36​

195​

18​

26​

16​

60​

47​

7​

0​

0​

0​

48​

0​

102​

27​

0​

21​

0​

0​

28​

27​

22​

125​

5​

0​

29​

0​

38​

72​

0​

0​

0​

31​

31​

19​

57​

0​

31​

0​

107​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

0​

16​

15​

0​

0​

0​

0​

0​

0​

31​

137​

0​

0​

0​

0​

137​

0​

0​

0​

0​

0​

0​

0​

0​

0​

9​

9​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

7​

I've left out the headings for privacy purposes. All the 7 I tried using COUNTA instead.

 

[Cubist]

You have 0's instead of blanks compared to your original post. They're not the same. Adjust the range.

=BYCOL(A1:A6,LAMBDA(col,COUNTIF(col,"<>0")))

 

[winds]

You have 0's instead of blanks compared to your original post. They're not the same. Adjust the range.

=BYCOL(A1:A6,LAMBDA(col,COUNTIF(col,"<>0")))

Ok getting somewhere now. How do I add a new formula so the headers is for column A instead of text, character and value?

 

[Cubist]

I don't understand what you mean.

 

[winds]

I don't understand what you mean.

If we go back to the first example, it accurately shows the criteria like reason and text however is it also possible to show the criteria in column A which is Alfred Betty and Charlie.

 

[Cubist]

Can you show what the expected results you want from the first example? It's unclear how you want to show it.

 

[winds]

Frequency	Headers	Additional column..
3	type, character, value	A,C,V
2	change, reason	C,V
2	text	A,C
5	-	C,V

This is in reference to the 2nd example that I edited.

And the last one I added where to see how many regardless of the other data surrounding it.