Looking to find most repeated text in a column on excel depending on criteria - 2 problems

[SW82SW]

I am part of a betting syndicate at work where we pick football teams to win their match every weekend. I keep a spreadsheet of the results and want to find the most picked team by each tipper and wondered if it was possible for it to show multiple answers in the same cell if there were multiple teams picked the same amount of times. If there was no team a tipper had picked multiple times then I would like it to show "all unique" or similar. I can get the most picked team but can't get it to show multiple teams or the "all unique" scenario.

I would also like to see the top 3 most successful picks and the 3 least successful picks for the group as a whole but I am not sure how to do this.

For my first problem, the most picked teams by tipster, (using Matthew as the example) I am using the following formula to return the most picked team. I am not sure how to return multiple answers in the same cell or show that all picks are unique:

=INDEX(Bets!$H11:$H108756,MODE(IF(Bets!$E11:$E108756="Matthew",IF(Bets!$H11:$H108756<>"",MATCH(Bets!$H11:$H108756,Bets!$H11:$H108756,{0,0})))))

For my second problem, the most and least successful teams picked by the group as a whole, I have used the following formula to return the most picked winning team and most picked losing team. "Y" is for a winning bet and "N" is for a losing bet:

=INDEX(Bets!$H11:$H108756,MODE(IF(Bets!$P11:$P108756="Y",IF(Bets!$H11:$H108756<>"",MATCH(Bets!$H11:$H108756,Bets!$H11:$H108756,{0,0})))))

=INDEX(Bets!$H11:$H108756,MODE(IF(Bets!$P11:$P108756="N",IF(Bets!$H11:$H108756<>"",MATCH(Bets!$H11:$H108756,Bets!$H11:$H108756,{0,0})))))

For all formulas column E is the tipper name, column H is the teams picked and column P is whether the pick won or not ("Y" or "N").

Thank you

 

[Peter_SSs]

I tried to use XL2BB but as I am using a work laptop it wouldn't allow me to use it.

So in future I suggest that you say that in your initial post so we don't keep asking.
Another option is to make up a small dummy worksheet with any irrelevant columns hidden and just copy/paste that into your post and tell us what columns they are. For example, from my 'Bets' sheet above, here is E10:H15 with columns F&G hidden

Name	Team
Matthew	a
John	d
Ann	d
Matthew	b
Matthew	a

 

[SW82SW]

So in future I suggest that you say that in your initial post so we don't keep asking.
Another option is to make up a small dummy worksheet with any irrelevant columns hidden and just copy/paste that into your post and tell us what columns they are. For example, from my 'Bets' sheet above, here is E10:H15 with columns F&G hidden

Name	Team
Matthew	a
John	d
Ann	d
Matthew	b
Matthew	a

Sorry Peter, I will bear that in mind in future.

Using your clipped example above I would just like to find the most frequent text in column H, and display this in one cell, whether there is one value or multiple most common values. Obviously, in this instance my result would be "a" and "d".

Thanks again.

 

[alansidman]

Here is an option, Create a GroupBy which is now part of 365 and then bring the data into power pivot and show the data as reflected in the pivot table.

Book9
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Team
2	Matthew	a		Name	Team	Team		Leader	Column Labels
3	John	d		Ann	d	1		Row Labels	(blank)	1	2	3	4
4	Ann	d		Ann	e	1		(blank)
5	Matthew	b		John	a	1		Ann		d, e
6	Matthew	a		John	c	3		John		a, d		c
7	John	c		John	d	1		Matthew			b		a, c, f
8	Matthew	b		Matthew	a	4
9	Matthew	c		Matthew	b	2
10	John	c		Matthew	c	4
11	Matthew	f		Matthew	f	4
12	Matthew	c
13	John	a
14	Matthew	a
15	Matthew	c
16	Matthew	a
17	John	c
18	Ann	e
19	Matthew	f
20	Matthew	f
21	Matthew	c
22	Matthew	f
Sheet1

Cell Formulas
Range	Formula
D2:F11	D2	=GROUPBY(A1:B22,B1:B22,COUNTA,3,0)
Dynamic array formulas.

The measure in power pivot is =CONCATENATEX('Range','Range'[Team],", ") which is in DAX

 

[Peter_SSs]

Here is an option, Create a GroupBy

Alan, perhaps you missed these..

@SW82SW do you have GROUPBY?

I don't think I do.

@SW82SW
See if this is what you want

=LET(h,Bets!H$11:H$1000,c,COUNTIFS(h,h),TEXTJOIN(", ",,UNIQUE(FILTER(h,c=MAX(c)))))

 

[SW82SW]

This works perfectly, thanks again.

 

[alansidman]

Peter
I did see that. I believe the OP may not realize that GroupBy is now available in 365 which is what they have indicated in their profile. I thought that showing that solution may prompt them to explore Excel a bit. But, I understand where you are coming from. Cheers.

 

[Peter_SSs]

This works perfectly, thanks again.

Cheers. Thanks for the confirmation.

Peter
I did see that. I believe the OP may not realize that GroupBy is now available in 365 which is what they have indicated in their profile.

Fair enough. Does everybody with 365 now have GROUPBY? I thought perhaps not, since the Help for it does not yet appear in this list where Help for all the other generally available functions is provided. (I know that the Help for GROUPBY is available elsewhere)

 

[Cubist]

Does everybody with 365 now have GROUPBY?

Enterprise licenses receive updates largely depends on the company's deployment schedule so not all 365 will have GROUPBY/PIVOTBY.