Forum Discussion

Ivan Predinsky's avatar
Ivan Predinsky
Copper Contributor
Oct 30, 2018

REMOVE DUPLICATES

Hi Guys,

 

I'm looking for someone who can help me with my problem.

 

I've got spreadsheet which is exported from different software and unfortunately this software is not able to delete duplicates for me.

 

I know I can go to DATA->REMOVE DUPLICATES and remove them but it's annoying to do it every single time + in future it won't be only me who will take care about this spreadsheet.

 

My question is : Is there any settings or rule which I can apply in order to remove duplicates from spreadsheet upon opening?

 

Thanks,

 

Ivan Predinsky

Excel
Formulas and Functions
office 365
    • Ivan Predinsky's avatar
      Ivan Predinsky
      Copper Contributor
      Oct 30, 2018

      Thanks mate.

       

      I sorted with Macro but anyway thanks for your help.

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Nov 01, 2018

    I would suggest you make a module for deleting duplicates - call this SUB after you exported data;

    or you can add the code after the 'export code' .

    try this first on a separate sheet - always make a copy of your work.

    hope this can help.

    thanks..

     

    Sub DelDupes()
     Sheets("Sheet1").Select
     lastrow = Cells(Rows.Count, "A").End(xlUp).Row
     Sheets("Sheet1").Range("A1:P" & lastrow).RemoveDuplicates _
     Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16), Header:=xlYes
    End Sub

    the above code is for up to Column P  - number 16th column
    the reason for Array(1... 16)
    you can change those two together with the Sheet Name to suit..


     

Resources