Forum Discussion

Chris_555's avatar
Chris_555
Copper Contributor
Oct 09, 2022

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:

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

  • dscheikey's avatar
    dscheikey
    Bronze Contributor

    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.

     

    • Chris_555's avatar
      Chris_555
      Copper Contributor

      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

       

       

      • SnowMan55's avatar
        SnowMan55
        Bronze 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:

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

Resources