Skip to main content

How to remove duplicates but leaving lowest value in another column in Excel?

Author: Sun Last Modified: 2023-10-27

For instance, there are two columns, A and B, column A includes some duplicate values, and column B contains some numbers, have you any way to remove duplicates from column A but leaving the lowest values in column B as below screenshot shown? This article will give you a favor.
doc remove duplicates leave lowest 1

Remove duplicate but keep lowest/highest value with Remove Duplicates and formula

Remove duplicate but keep lowest/highest value with Kutools for Excel good idea3


Remove duplicate but keep lowest/highest value with Remove Duplicates and formula

To remove duplicate but keep lowest value, you can apply Remove Duplicates function and a formula.

1. Select and copy column A which contains the duplicate values, and paste them to another place. See screenshot:
doc remove duplicates leave lowest 2

2. Then select the values in the pasted list, and click Data > Remove Duplicates, and check Continue with the current selection option from the popped out dialog. Click Remove Duplicates, then in the Remove Duplicates dialog, check the column you want to remove duplicates from. See screenshot:
doc remove duplicates leave lowest 3

3. Then click OK, and a dialog pops out to remind you how many duplicates have been removed, click OK to close it.
doc remove duplicates leave lowest 4

4. Select a cell next to the list you have removed the duplicates from, enter this formula =MIN(IF($A$2:$A$12=D2,$B$2:$B$12)), press Shift + Ctrl + Enter keys, and drag fill handle down to the cell you need. See screenshot:
doc remove duplicates leave lowest 5

Now all duplicates have been removed but the lowest values leaved in another column based on the duplicates.
doc remove duplicates leave lowest 6

Note:

1. If you want to remove all duplicates but leave the highest ones, you can apply this formula =MAX(IF($A$2:$A$12=D2,$B$2:$B$12)), remember to press Shift + Ctrl + Enter keys.

2. In the above formulas, A2:A12 is the original list you need to remove duplicates from.


Remove duplicate but keep lowest/highest value with Kutools for Excel

If you have Kutools for Excel, you can quickly remove duplicates but only keep the lowest or highest value with its Advanced Combine Rows function.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 

After installing Kutools for Excel, please do as below:( Free Download Kutools for Excel Now!)

1. Before applying Kutools, you had better to have a copy of the original data. Then select the range cells you want to remove duplicates, and click Kutools > Merge & Split > Advanced Combine Rows. See screenshot:
doc remove duplicates leave lowest 7

2. In the Advanced Combine Rows dialog, select the column you want to remove duplicates from, and then click Primary Key to set it as primary key column, and then select the column you want to leave the lowest or highest values in, and click Calculate to select Min or Max as you need. See screenshot:
doc remove duplicates leave lowest 8

3. Click Ok, then the duplicate values have been deleted but keep the lowest one.
doc remove duplicates leave lowest 9

Tip.with Kutools for Excel’s Advanced Combine Rows, you also can remove duplicate values and then sum numbers in another column as below screenshot shown.If you want to have a free trial of the Extract Text function, please go to free download Kutools for Excel first, and then go to apply the operation according above steps.
doc remove duplicates leave lowest 10

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (4)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This is so helpful. Thanks
This comment was minimized by the moderator on the site
Hii, This formula works for me only for the first value. And not fr the rest
This comment was minimized by the moderator on the site
Thank you, this is what I needed. I am working on removing duplicates of lower quantity, (keeping the highest quantity)

How do I tweak this code to cover two 'product' columns instead of one? (e.g. part number and colour id in column A and B respectively) with quantity in column C.

E.g.
Part. Color. Quantity
1. 1. 2
1. 4. 3
1. 14. 3
1. 15. 3
1. 15. 2
1. 15. 1
2. 1. 1
2. 4. 2

Would be removing only:
1. 15. 2
1. 15. 1

Thanks

Anton
This comment was minimized by the moderator on the site
This may be useful, but is far more complicated than what I do to solve this problem:

simply sort by Column A (Product) ascending and Column B (Sales) ascending

then launch "Remove duplicates" and select only to verify duplicate values in column A



Your done!

Excel by default maintains the first row and delete all duplicates after that row
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations