Excel How to Remove Duplicate Rows in multiple of 3 Same Value

Question

Would it be possible for Excel to eliminate rows that are duplicates? If I have three identical values, the function will maintain one duplicate and remove the other two. If I have more than three identical values, it will keep two duplicates. It will keep 3 duplicates if there are 7 duplicates, and so on. For instance:

This is the original data that I want to remove:

This is how it should look like after the duplicates are removed along with the rows

Can VBA, formula, or other excel features do this?

Answer 1

Put the following formula into a helper column, eg put it into cell D2 and copy down =MOD(COUNTIF($A$2:$A2,A2),3)=1

• The CountIf will count the number of the same values until the current row
• The Mod will divide this number by 3 and give you the remainder (1, 2, 0, 1, 2, 0...)
• The =1 will now show True for the 1st, 4th, and 7th... rows with the same value and False for all the other rows (that you want to delete).

Now filter for the value FALSE of the helper column and delete all visible rows.