SOLVED

Deleting rows

Copper Contributor

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. 

6 Replies
You need VBA macro.

@Chris_555 

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.

 

@dscheikey 

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

 

 

best response confirmed by Hans Vogelaar (MVP)
Solution

 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:

https://imgur.com/Ob3AFQ2 

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

My actual data resides over 6 columns.
I will try your suggestion and see if it works.
Thankyou for your assistance
Thankyou.

I have tried out your suggestions and it works.

Again thankyou all for helping to solve my problem.
1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

 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:

https://imgur.com/Ob3AFQ2 

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

View solution in original post