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

0 votes

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?

Dec 28, 2022 in Others by Kithuzzz
• 38,010 points
329 views

1 answer to this question.

0 votes

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.

answered Dec 28, 2022 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,420 points
1,789 views
0 votes
1 answer

How to retrieve the value of a text field in Flutter App?

Hi@akhtar, In your TextField, you can call one ...READ MORE

answered Sep 3, 2020 in Others by MD
• 95,440 points
1,506 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10, 2022 in Others by Soham
• 9,700 points
881 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY ...READ MORE

answered Jun 21, 2022 in Others by nisha
• 2,210 points
462 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
876 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,183 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
480 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
731 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,420 points
508 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,546 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP