+ Reply to Thread
Results 1 to 7 of 7

Remove only a certain number of duplicate values

  1. #1
    Registered User
    Join Date
    07-22-2008
    Location
    WV
    Posts
    63

    Remove only a certain number of duplicate values

    My duplicate values are account numbers. I also have a date column sorted newest to oldest. I want to keep the three most recent duplicated account numbers based on the date, but remove any duplicate older than the first three. If I go to Data Tools > Remove Duplicates it removes all but the newest one. How can I remove all except the first three newest ones?

    Attached is a sample of the data I am working with. Thanks.

    duplicate sample.xlsx

  2. #2
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Remove only a certain number of duplicate values

    1 possibility is with this code

    Please Login or Register  to view this content.
    Kind regards
    Leo
    Attached Files Attached Files

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Remove only a certain number of duplicate values

    Hi,

    See my example code and attached. You must sort by Date from Large to Small and add the formulas to Column C before you run my code. It does what Leo's does above but might make more sense.

    Please Login or Register  to view this content.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Forum Expert
    Join Date
    08-16-2015
    Location
    Antwerpen, Belgium
    MS-Off Ver
    2007-2016
    Posts
    2,380

    Re: Remove only a certain number of duplicate values

    @ MarvinP

    It does what Leo's does above but might make more sense.
    why ?

    Kind regards
    Leo

  5. #5
    Registered User
    Join Date
    12-09-2015
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    7

    Re: Remove only a certain number of duplicate values

    Based on your example, sort the data by date, descending.

    Enter this formula into column C - =COUNTIF($A$3:A3,A3) - and copy-down as far as your data goes. This formula counts the number of times the account number in that row is repeated in the list above it, kind of giving you a rank based on the combination of that account number and date.

    Next, paste this formula into column D - =IF(C3<=3,A3,"") - and this one into column E - =IF(C3<=3,B3,""). This means that the account number and date will only be shown in these cells if the rank (from column C) is 3 or less. This gives you the 3 newest dates for each account number. The other duplicates will be blank.

    You could then filter the column to remove blanks, or paste the values somewhere else and use the remove duplicates function.

    Here are my workings - duplicate sample v2.xlsx
    We'll handle Excel for you - excelulous.com

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,111

    Re: Remove only a certain number of duplicate values

    Hi Leo,

    When I create variable names, I try to use words that are meaningful to everyone. In your code you use "lr" and I use "LastRow". I've created a variable called RowCtr (Row Counter) and your variable for the same thing is named "x".

    You built a formula in the A column of Range("A2", "A" & lr).FormulaR1C1 = "=COUNTIF(R2C2:RC[+1],RC[+1])" (after inserting a blank column A) and I did my work manually in the first blank column.
    Your code sorted the table by date and I did it manually before I ran my simpler macro.

    Both our code accomplished what the OP wanted but I'm thinking my code was simpler and might "make more sense".
    Last edited by MarvinP; 02-06-2016 at 05:56 PM.

  7. #7
    Registered User
    Join Date
    07-22-2008
    Location
    WV
    Posts
    63

    Re: Remove only a certain number of duplicate values

    Sorry for the delay in replying. I had to put this project on hold. Now that I am back on it, I'm glad your solution works. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to sum duplicate values then remove the duplicate rows?
    By terry3218 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-12-2015, 02:38 AM
  2. Remove duplicate number values but retain text value via vba
    By kosherboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2015, 03:42 PM
  3. [SOLVED] Remove duplicate values from Listbox
    By TPDave in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-15-2014, 05:21 AM
  4. combine & remove duplicate part number
    By integra in forum Excel General
    Replies: 1
    Last Post: 08-19-2013, 06:08 PM
  5. remove duplicate values from array
    By nikko50 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2013, 07:14 PM
  6. [SOLVED] Remove Duplicate Values and Blanks in Row
    By mrmoc85 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2012, 11:25 PM
  7. Excel 2007 : Remove Duplicate Values from top 5
    By loknath in forum Excel General
    Replies: 1
    Last Post: 11-08-2011, 09:46 AM

Tags for this Thread

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