Return the most frequently-appearing corresponding value based on given criterion

[megera716]

I have dates in Column A, person's name in B, locations in C. Each date, a name is assigned to a location, but that is not my circus

At the end of the month, I need to find out which location each name was assigned to the most.

The data is basically exactly like this but with 100+ potential names and 30 potential locations.

What I would like is something off to the side that just tells me which value was the most frequently occurring for each name. If it's a tie between locations, it's okay if it returns an error (or maybe it says "Manual") and then the person who does own this circus can use their judgment to decide.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F
1	Date	Name	Location		Name	Most Frequent Location
2	10-Jul	Name 1	Z		Name 1	Z
3	10-Jul	Name 2	Y		Name 2	Y
4	10-Jul	Name 3	X		Name 3	X
5	11-Jul	Name 1	Y
6	11-Jul	Name 2	X
7	11-Jul	Name 3	X
8	12-Jul	Name 1	Z
9	12-Jul	Name 2	Y
10	12-Jul	Name 3	X
11	13-Jul	Name 1	X
12	13-Jul	Name 2	Y
13	13-Jul	Name 3	Z
Sheet10

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($C$2:$C$13,MODE(IF($B$2:$B$13=E2,{1,1}*MATCH($C$2:$C$13,$C$2:$C$13,0))))

 

[felixstraube]

What if two or more locations are equally frequent? wanna show them all? how? in one cell concatenated, in different cells?

 

[felixstraube]

Taking that into account, how about?:

Location are randomly generated for testing (press F9 to update), and columns G:I are just for control.

Freq.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Name	Location		Name	Most frequent location	X	Y	Z
2	10/07/2024	Name1	X		Name1	Y	1	5	4
3	10/07/2024	Name2	Z		Name2	Y	2	5	3
4	10/07/2024	Name3	X		Name3	X, Z	4	2	4
5	11/07/2024	Name1	Z
6	11/07/2024	Name2	Y
7	11/07/2024	Name3	X
8	12/07/2024	Name1	Y
9	12/07/2024	Name2	Y
10	12/07/2024	Name3	X
11	13/07/2024	Name1	Z
12	13/07/2024	Name2	Y
13	13/07/2024	Name3	Z
14	14/07/2024	Name1	Y
15	14/07/2024	Name2	Z
16	14/07/2024	Name3	Z
17	15/07/2024	Name1	Z
18	15/07/2024	Name2	X
19	15/07/2024	Name3	Z
20	16/07/2024	Name1	Y
21	16/07/2024	Name2	X
22	16/07/2024	Name3	Y
23	17/07/2024	Name1	Y
24	17/07/2024	Name2	Y
25	17/07/2024	Name3	Y
26	18/07/2024	Name1	Z
27	18/07/2024	Name2	Y
28	18/07/2024	Name3	X
29	19/07/2024	Name1	Y
30	19/07/2024	Name2	Z
31	19/07/2024	Name3	Z
Sheet2

Cell Formulas
Range	Formula
C2:C31	C2	=CHAR(RANDARRAY(30,1,88,90, TRUE))
F2:F4	F2	=LET(d, $B$2:$C$31, n, CHOOSECOLS(d, 1), l,CHOOSECOLS(d, 2), fl, FILTER(l,n=E2), f, MATCH(fl, fl,0), fr, DROP(FREQUENCY(f, f), -1), i, FILTER(f, fr=MAX(fr)), TEXTJOIN(", ", ,INDEX(fl, i)) )
G2:I4	G2	=SUM(($B$2:$B$31=$E2)*($C$2:$C$31=G$1))
A14:A31	A14	=A11+1
Dynamic array formulas.

 

[megera716]

Try:

Book1
	A	B	C	D	E	F
1	Date	Name	Location		Name	Most Frequent Location
2	10-Jul	Name 1	Z		Name 1	Z
3	10-Jul	Name 2	Y		Name 2	Y
4	10-Jul	Name 3	X		Name 3	X
5	11-Jul	Name 1	Y
6	11-Jul	Name 2	X
7	11-Jul	Name 3	X
8	12-Jul	Name 1	Z
9	12-Jul	Name 2	Y
10	12-Jul	Name 3	X
11	13-Jul	Name 1	X
12	13-Jul	Name 2	Y
13	13-Jul	Name 3	Z
Sheet10

Cell Formulas
Range	Formula
F2:F4	F2	=INDEX($C$2:$C$13,MODE(IF($B$2:$B$13=E2,{1,1}*MATCH($C$2:$C$13,$C$2:$C$13,0))))

@Eric W , this worked perfectly, thank you!

Would you mind explaining how it works? I understand INDEX of range C and if B contains the value in E and what MODE does, but the 1s in curly brackets and why the MATCH is also referencing column C has me all (when I use INDEX/MATCH, the column I am INDEXing isn't part of the MATCH criteria!)

 

[felixstraube]

@megera716 did you read this:

What if two or more locations are equally frequent? wanna show them all? how? in one cell concatenated, in different cells?

 

[Eric W]

Sure -

=INDEX($C$2:$C$13,MODE(IF($B$2:$B$13=E2,{1,1}*MATCH($C$2:$C$13,$C$2:$C$13,0))))

The part in red checks for your criteria. If it finds a row that matches, it goes to the MATCH part. The MATCH returns the index of the first match it finds. So if you compare the list against itself (C2:C13 against C2:C13) and the list is {A,B,A,B,A}, it returns {1,2,1,2,1}, since when it looks at the second and third As, they both find the first A. So the return list shows the first location of each individual item. Then MODE returns the most common number, 1 in this example. Then finally the INDEX uses that number to look into the range and return the actual value.

The {1,1} part is to avoid an error that MODE can cause. If there's only one matching value, so that the result list looks like {3}, then MODE will cause an error because there's only 1 number. Multiplying {3} by {1,1} results in {3,3}, and MODE will return 3. If there are more results, multiplying by {1,1} doesn't hurt since each result is repeated by the same amount.

Felix raises a good question, is it possible that there will be more than 1 most common value? He offers one solution, here's a way to update my formula to include multiple modes using MODE.MULT:

Book1
	A	B	C	D	E	F	G
1	Date	Name	Location		Name	Most Frequent Location
2	10-Jul	Name 1	Z		Name 1	Z	Y
3	10-Jul	Name 2	Y		Name 2	Y
4	10-Jul	Name 3	X		Name 3	X
5	11-Jul	Name 1	Y
6	11-Jul	Name 2	X
7	11-Jul	Name 3	X
8	12-Jul	Name 1	Z
9	12-Jul	Name 2	Y
10	12-Jul	Name 3	X
11	13-Jul	Name 1	Y
12	13-Jul	Name 2	Y
13	13-Jul	Name 3	Z
14
Sheet10

Cell Formulas
Range	Formula
F2:G2,F3:F4	F2	=INDEX($C$2:$C$13,TRANSPOSE(MODE.MULT(IF($B$2:$B$13=E2,{1,1}*MATCH($C$2:$C$13,$C$2:$C$13,0)))))
Dynamic array formulas.

Multiple modes will just spill to the right, or we can use TEXTJOIN to put them in a single cell. Hope this helps!