Home

REMOVE DUPLICATES

%3CLINGO-SUB%20id%3D%22lingo-sub-279786%22%20slang%3D%22en-US%22%3EREMOVE%20DUPLICATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279786%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20someone%20who%20can%20help%20me%20with%20my%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20got%20spreadsheet%20which%20is%20exported%20from%20different%20software%20and%20unfortunately%20this%20software%20is%20not%20able%20to%20delete%20duplicates%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20can%20go%20to%20DATA-%26gt%3BREMOVE%20DUPLICATES%20and%20remove%20them%20but%20it's%20annoying%20to%20do%20it%20every%20single%20time%20%2B%20in%20future%20it%20won't%20be%20only%20me%20who%20will%20take%20care%20about%20this%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%20%3A%20Is%20there%20any%20settings%20or%20rule%20which%20I%20can%20apply%20in%20order%20to%20remove%20duplicates%20from%20spreadsheet%20upon%20opening%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIvan%20Predinsky%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-279786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-281435%22%20slang%3D%22en-US%22%3ERe%3A%20REMOVE%20DUPLICATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-281435%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20suggest%20you%20make%20a%20module%20for%20deleting%20duplicates%20-%20call%20this%20SUB%20after%20you%20exported%20data%3B%3C%2FP%3E%3CP%3Eor%20you%20can%20add%20the%20code%20after%20the%20'export%20code'%20.%3C%2FP%3E%3CP%3E%3CEM%3Etry%20this%20first%20on%20a%20separate%20sheet%20-%20always%20make%20a%20copy%20of%20your%20work.%3C%2FEM%3E%3C%2FP%3E%3CP%3Ehope%20this%20can%20help.%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20DelDupes()%3CBR%20%2F%3E%20Sheets(%22Sheet1%22).Select%3CBR%20%2F%3E%20lastrow%20%3D%20Cells(Rows.Count%2C%20%22A%22).End(xlUp).Row%3CBR%20%2F%3E%20Sheets(%22Sheet1%22).Range(%22A1%3AP%22%20%26amp%3B%20lastrow).RemoveDuplicates%20_%3CBR%20%2F%3E%20Columns%3A%3DArray(1%2C%202%2C%203%2C%204%2C%205%2C%206%2C%207%2C%208%2C%209%2C%2010%2C%2011%2C%2012%2C%2013%2C%2014%2C%2015%2C%2016)%2C%20Header%3A%3DxlYes%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3Ethe%20above%20code%20is%20for%20up%20to%20Column%20P%20%20-%20number%2016th%20column%3CBR%20%2F%3Ethe%20reason%20for%20Array(1...%2016)%3CBR%20%2F%3Eyou%20can%20change%20those%20two%20together%20with%20the%20Sheet%20Name%20to%20suit..%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279846%22%20slang%3D%22en-US%22%3ERe%3A%20REMOVE%20DUPLICATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279846%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20mate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sorted%20with%20Macro%20but%20anyway%20thanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279790%22%20slang%3D%22en-US%22%3ERe%3A%20REMOVE%20DUPLICATES%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279790%22%20slang%3D%22en-US%22%3E%3CP%3ETry%20this%20%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4032-excel-dynamic-list-of-unique-values.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F4032-excel-dynamic-list-of-unique-values.html%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ivan Predinsky
New Contributor

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

3 Replies

Thanks mate.

 

I sorted with Macro but anyway thanks for your help.

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