Closed Thread
Results 1 to 10 of 10

Remove 'duplicates' to leaving lowest value

  1. #1
    Registered User
    Join Date
    07-31-2009
    Location
    London, England
    MS-Off Ver
    Office 2007
    Posts
    4

    Remove 'duplicates' to leaving lowest value

    Hi,

    I have a spreadsheet (excel 2007) than contains duplicated reference numbers in one column with varying numerical values in another.

    example

    Col A Col B
    22124 4.20
    22124 8.60
    22124 9.75
    22124 3.15
    22125 7.60
    22125 26.00
    22125 1.78

    I want to delete all duplicates in column A but leaving the the row with the lowest numerical value in column B (or extract this information to another sheet).

    In the above example I'd be left with:

    22124 3.15
    22125 1.78

    Is there a simple way of doing this? I'm trying to it manually but there are almost 500,000 rows of data!

    Any help appreciated.

    TIA
    Last edited by alseeon; 03-08-2010 at 05:01 PM.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Remove 'duplicates' to leaving lowest value

    You can create Pivot table.. in rows put your data and define values as MIN
    Attached Files Attached Files

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Remove 'duplicates' to leaving lowest value

    Copy your data in columnA to columnD > click Data tab in the menu > click Remove Duplicates.

    In Column E1: =MIN(IF($A$1:$A$7=D1,$B$1:$B$7))

    ctrl+shift+enter, not just enter

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Remove 'duplicates' to leaving lowest value

    If you are happy to run a macro you could try

    Please Login or Register  to view this content.
    Test file attached
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-31-2009
    Location
    London, England
    MS-Off Ver
    Office 2007
    Posts
    4

    Re: Remove 'duplicates' to leaving lowest value

    Thanks all for your suggestions.

    Marcol,
    Great macro, thanks for very much for your time. It's much appreciated.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Remove 'duplicates' to leaving lowest value

    Glad to have helped

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

    Thanks

  7. #7
    Registered User
    Join Date
    07-31-2009
    Location
    London, England
    MS-Off Ver
    Office 2007
    Posts
    4

    Re: Remove 'duplicates' to leaving lowest value

    Hi Marcol,

    I'm struggling to amend your macro to suit my needs.

    My fault entirely, I didn't expect anybody to go to that amount of trouble. So I posted column A & column B as examples. The actual references are in 'J' and the values are in 'L'.

    I've tried to amend your macro to suit but I'm afraid I'm a bit of a thicko with coding. Any chance you (or anybody) could help with the changes needed.

    Sample data attached.

    TIA
    Attached Files Attached Files

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Remove 'duplicates' to leaving lowest value

    No problem,

    I have your file. I can't do it right away. Look back in a few hours time.

    Regards
    Alistair

    Hold that result!

    The file is in 2003 format Just save it as 2007 .xlsm (macro enabled)
    The code is amended to suit 2007

    Regards Alistair


    If you are happy with any members response, please consider using the scales icon top right of their post
    Attached Files Attached Files
    Last edited by Marcol; 03-08-2010 at 04:24 PM. Reason: Managed to do it earlier than I thought

  9. #9
    Registered User
    Join Date
    04-20-2012
    Location
    Bucharest, Romania
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Remove 'duplicates' to leaving lowest value

    Hello,

    I need the same thing. I have an excel file and i want to remove all duplicates line by Colum A(EAN in my excel) and keep lowest value of Column E( Resell price PLN nett).

    Is someting like this:
    5901347068935 RS14-BKD900 SS14 MEDICINE 24.4 99.9
    5901347068935 RS14-BKD900 SS14 MEDICINE 25.4 99.9
    5901347068935 RS14-BKD900 SS14 MEDICINE 27.1 99.9

    I want to keep only

    5901347068935 RS14-BKD900 SS14 MEDICINE 24.4 99.9


    I attached the file. Thank you so much in advance.
    Attached Files Attached Files

  10. #10
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: Remove 'duplicates' to leaving lowest value

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1