Find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values

[Analysis116]

I would like to find the maximum (largest) value in a range of cells and return all values for multiple occurrences/duplicate maximum (largest) values for each user in excel.

Below is a list of the count of trips made by each user ID (user 1, 2, 3, etc. ) to purchase each brand of chocolate.
For example for user 1, he has purchased both Hersheys and Reeses three times (duplicate maximum values). If the Xlookup function is used with the MAX function, then only the brand corresponding to the first MAX value will return, Hersheys, and not Reeses. For user 2 you can see that he has purchased Hersheys, Reeses and Snickers all once. Thus, there are also multiple maximum values in that case also. I would like to return all maximum values if there are multiple occurrences of the maximum value.

Separately, I would also like to return the value for the nth maximum value.

Here is an example data set:

User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User
1​	3​	3​	0​	0​
2​	1​	1​	1​	0​
3​	4​	4​	1​	1​
4​	0​	0​	1​	0​
5​	2​	1​	2​	0​

 

[Peter_SSs]

Welcome to the MrExcel board!

Always best to include your expected results with your sample data if possible, then helpers know exactly what you are aiming for.

Also, suggest that you investigate XL2BB for providing sample data & expected results to make it easier for helpers to understand just what you have & where it is and also what you want & where it is to be.

Is this what you want?

20 11 11.xlsm
	A	B	C	D	E	F
1	User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User
2	1	3	3	0	0	Hersheys, Reeses
3	2	1	1	1	0	Hersheys, Reeses, Snickers
4	3	4	4	1	1	Hersheys, Reeses
5	4	0	0	1	0	Snickers
6	5	2	1	2	0	Hersheys, Snickers
Max

Cell Formulas
Range	Formula
F2:F6	F2	=TEXTJOIN(", ",1,FILTER(B$1:E$1,B2:E2=MAX(B2:E2)))

 

[Analysis116]

Hi Peter,
Thank you very much for your quick and helpful reply.
I will also look to download XL2BB hopefully this weekend for providing sample data as I have to read about that further.

I do have some follow up questions on your response if you would not mind to please let me know your thoughts.

User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User
1​	3​	3​	0​	0​	Hersheys
2​	0​	1​	1​	1​	Reeses
3​	4​	4​	1​	1​	Reeses
4​	0​	0​	1​	0​	Snickers
5​	2​	1​	2​	0​	Snickers

1. How would I filter for only the trips where the number of candy brands purchased most often is more than one? Also the reverse, how would I filter for only the trips where the number of candy brands purchased most often is ONLY one unique brand?
I am trying to find what percent of the trips have multiple occurences of a brand purchased most often. I am using a large data set.

2. How would I identify only users who have purchased only one brand most often?
Also the reverse, how would I identify only users who have purchased more than one different brand most often?

3. If only one brand can be chosen in this example, would you have any recommendations on how to assign a single brand (other than the first occuring one in the data set)?

Thank you!

 

[Peter_SSs]

I think to be sure that I would understand what you want, I would like to see some sample data and the expected results of each of the circumstances that you are describing.

I can't see that the table in post 3 is telling me anything?

 

[Analysis116]

Yes, good point.

I'm going to start with this main question:

How would I filter for only the trips where the number of candy brands purchased most often is more than one?

Overall goal: I am trying to find what percent of the trips have multiple occurences of a brand purchased most often.

Expected Results:

User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User	More than One Highest Number of trips purchasing candy brand		Total Number of Trips for Users have more than one different brand where they had the highest number of trips:
1​	3​	3​	0​	0​	Hersheys, Reeses	Yes		Answer here
2​	0​	1​	1​	1​	Reeses, Snickers, Twix	Yes
3​	4​	4​	1​	1​	Hersheys, Reeses	Yes
4​	0​	0​	5​	0​	Snickers	No
5​	2​	1​	2​	0​	Hershey, Snickers	Yes

 

[Peter_SSs]

You have a space for the answers, but you haven't entered the answers.

I would like to see some sample data and the expected results

 

[Analysis116]

True!

User ID	Hersheys	Reeses	Snickers	Twix	Candy Brand with the Highest Number of Trips for User	More than One Highest Number of trips purchasing candy brand	Total Number of Trips for Users have more than one different brand where they had the highest number of trips:
1​	3​	3​	0​	0​	Hersheys, Reeses	Yes	6​
2​	0​	1​	1​	1​	Reeses, Snickers, Twix	Yes	3​
3​	4​	4​	1​	1​	Hersheys, Reeses	Yes	8​
4​	0​	0​	5​	0​	Snickers	No	0​
5​	2​	1​	2​	0​	Hershey, Snickers	Yes	4​

 

[Peter_SSs]

Thanks. Like this?

20 11 12.xlsm
	A	B	C	D	E			H
1	User ID	Hersheys	Reeses	Snickers	Twix			Total Number of Trips for Users have more than one different brand where they had the highest number of trips:
2	1	3	3	0	0			6
3	2	0	1	1	1			3
4	3	4	4	1	1			8
5	4	0	0	5	0			0
6	5	2	1	2	0			4
Sheet2 (2)

Cell Formulas
Range	Formula
H2:H6	H2	=IF(COUNTIF(B2:E2,MAX(B2:E2))>1,SUMIF(B2:E2,MAX(B2:E2)),0)

 

[Analysis116]

Exactly! Thank you for your help!

May I ask how the formula is evaluated in sentence format?

For Row 2 for example.

Is it saying the following?

IF this is true:
Evaluate this formula:

Count the number of cells in range B2:E2 where the MAX is equal to 3 (2 cells). If this number is greater than 1 (since we only want to sum trips that have more than one maximum value) then sum the values of the trip in the range B2:E2.

If this is false:
Then return 0

 

[Analysis116]

Exactly! Thank you for your help!

May I ask how the formula is evaluated in sentence format?

For Row 2 for example.

Is it saying the following?

IF this is true:
Evaluate this formula:

Count the number of cells in range B2:E2 where the MAX is equal to 3 (2 cells). If this number is greater than 1 (since we only want to sum trips that have more than one maximum value) then sum the values of the trip in the range B2:E2.

If this is false:
Then return 0

Actually I meant to say:

Count the number of cells in range B2:E2 that have a value of 3 (2 cells) since the MAX of range B2:E2 is 3.