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.

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

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