Forum Discussion
Chris_555
Oct 09, 2022Copper Contributor
Deleting rows
Hi Everyone,
I would like to know if it is possible for Excel to automatically delete all occurrences of a row that contains the same text.
As an example if a a worksheet contained the following:
Help
Me
Out
Help
If
Help
Again is it possible for Excel to locate all the occurrences of the word "Help" and delete all three occurrences in one go.
Does your data reside in a single column? If so, you can readily come up with a subset of the unique values, without sorting, by using a combination of COUNTIF and FILTER functions:
The top two gray cells contain the formulas shown above them in their respective columns. Copy the column B formula down through the last of the data. rows.
And perhaps that can be written without the use of the additional column (B).
- dscheikeyBronze Contributor
Do you really want to delete all three occurrences of "Help" or may one remain?
If you only want to delete the duplicates, there is a special function for this in the menu in the Data area, you will find "Remove Duplicates". Could this help you? It is not 100% automatic, because you have to click twice in the menu.
- Chris_555Copper Contributor
Thank you for your response.
I would like to delete all three occurrences of the word "help".
I used the above as an example.
In my actual spreadsheet there will be multiple duplications and if I can delete all different duplications that would be great.
The spreadsheet may look more like this:
help
go
help
in
go
out
help
I can sort the list so that all occurrences are together but I would still have to go through the spreadsheet A:Z to find them.
I was hoping that there was a function that would sort through the spreadsheet and find all duplicate rows and then delete all occurrences of the row weather it be in rows 1 and 2 or rows 501 and 502.
Thankyou
- SnowMan55Bronze Contributor
Does your data reside in a single column? If so, you can readily come up with a subset of the unique values, without sorting, by using a combination of COUNTIF and FILTER functions:
The top two gray cells contain the formulas shown above them in their respective columns. Copy the column B formula down through the last of the data. rows.
And perhaps that can be written without the use of the additional column (B).
- Harun24HRBronze ContributorYou need VBA macro.