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

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 IDHersheysReesesSnickersTwixCandy 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​
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1User IDHersheysReesesSnickersTwixCandy Brand with the Highest Number of Trips for User
213300Hersheys, Reeses
321110Hersheys, Reeses, Snickers
434411Hersheys, Reeses
540010Snickers
652120Hersheys, Snickers
Max
Cell Formulas
RangeFormula
F2:F6F2=TEXTJOIN(", ",1,FILTER(B$1:E$1,B2:E2=MAX(B2:E2)))
 

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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 IDHersheysReesesSnickersTwixCandy 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 IDHersheysReesesSnickersTwixCandy Brand with the Highest Number of Trips for UserMore than One Highest Number of trips purchasing candy brandTotal 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, ReesesYesAnswer here
2​
0​
1​
1​
1​
Reeses, Snickers, TwixYes
3​
4​
4​
1​
1​
Hersheys, ReesesYes
4​
0​
0​
5​
0​
SnickersNo
5​
2​
1​
2​
0​
Hershey, SnickersYes
 

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

True!

User IDHersheysReesesSnickersTwixCandy Brand with the Highest Number of Trips for UserMore than One Highest Number of trips purchasing candy brandTotal 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, ReesesYes
6​
2​
0​
1​
1​
1​
Reeses, Snickers, TwixYes
3​
3​
4​
4​
1​
1​
Hersheys, ReesesYes
8​
4​
0​
0​
5​
0​
SnickersNo
0​
5​
2​
1​
2​
0​
Hershey, SnickersYes
4​
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Thanks. Like this?

20 11 12.xlsm
ABCDEH
1User IDHersheysReesesSnickersTwixTotal Number of Trips for Users have more than one different brand where they had the highest number of trips:
2133006
3201113
4344118
5400500
6521204
Sheet2 (2)
Cell Formulas
RangeFormula
H2:H6H2=IF(COUNTIF(B2:E2,MAX(B2:E2))>1,SUMIF(B2:E2,MAX(B2:E2)),0)
 
Solution

Analysis116

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,011
Messages
5,599,331
Members
414,305
Latest member
scarletX

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top