Remove only a certain number of duplicate values

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

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

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

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

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

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