I would like to know if there's a possibility for excel to remove duplicates rows if I have 3 same value, the function will remove 2 duplicate and keep 1, however if the same value is greater than 3, it will keep 2 duplicates. If it has 7 duplicates, it will keep 3 duplicates and so on... For example:

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 feature could do this?

CodePudding user response：

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 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, 7th... row 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.

If this is a repeated process, you can write a VBA routine to do this.

CodePudding user response：

You can use Excel formulas. Use 3 helper columns

• Formula1 COUNTIFS(A:A,A2)
• Formula2 COUNTIFS($D$2:D2,D2)
• 3 interval IF((E2-QUOTIENT(E2,3)*3)=1,1,"")

CodePudding user response：

=FILTER(A2:C18,MOD(ROW(A2:C18)-MATCH(A2:A18,A2:A18,0),3)=1)

if you have Excel 365, assuming the data is sorted on column A and starts in row 2.

or

=FILTER(A2:C18,MOD(SEQUENCE(ROWS(A2:C18),1,2)-XMATCH(A2:A18,A2:A18),3)=1)

If you need to filter in-place, just use the inner part of either of these as the filter column, e.g.

=MOD(SEQUENCE(ROWS(A2:C18),1,2)-XMATCH(A2:A18,A2:A18),3)=1