How to find max or min value based on unique values in Excel?
For example, you have two columns of data: one with names (including duplicates) and the other with scores. Now, you want to find the maximum score for each unique name, as shown in the screenshot below. How can you quickly accomplish this task in Excel? This article provides the solution.
Find max or min value based on unique values by creating a PivotTable
Find max or min value based on unique values with Kutools for Excel 
Find max or min value based on unique values by creating a PivotTable
In Excel, PivotTable is a powerful function, you can create a pivot table based on the range, and then find the max or min value by unique values.
1. Select the data you want to find max or min value from, and click Insert > PivotTable > PivotTable. See screenshot:
2. Then check New Worksheet or Existing Worksheet in Create PivotTable dialog as you need, here I check New Worksheet. See screenshot:
3. Click OK. Now a PivotTable Field List pane appears in a new sheet, and check Name (the column you want to select max or min values based on) and right click to Add to Row Labels, and check Score (the column you want to return to max or min value) and right click to Add to Values. See screenshots:
4. Then you can see a pivot table created in the worksheet, and now right click at the Sum of Score and select Value Field Settings from context menu, then in the popping dialog, under Summarize Values By tab, select Max or Min as you need. See screenshots:
5. Click OK. Now the maximum values for each unique value have been found.
Find max or min value based on unique values with Kutools for Excel
With Kutools for Excel, you can use the Advanced Combine Rows utility to quickly find the max or min value based on unique values.
1. Select the data that you want to find max or min value (if you want to keep the original data, you can copy and paste them to another location first), and then click Kutools > Merge & Split > Advanced Combine Rows. See screenshot:
2. Then in the Advanced Combine Rows dialog, do as below:
1) Select the column that you want to get the unique value, then click Primary Key to set it as primary key column. See screenshot:
2) Then select the column which you want to return the max or min value from, and click Calculate > Max or Min to find the max or min value. See screenshot:
3. Now click Ok, and you can see the max or min values of each unique value are found out. See screenshot:
Tip: The "Use formatted values" option will keep your value formatting after combining.
Click here to know more about Advanced Combine Rows.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in