Delete repeating records.

%3CLINGO-SUB%20id%3D%22lingo-sub-2951044%22%20slang%3D%22en-US%22%3EDelete%20repeating%20records.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2951044%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3CBR%20%2F%3EI%20would%20like%20to%20know%20how%20I%20can%20remove%20the%20duplicate%20data%20including%20the%20main%20one.%20that%20is%2C%20leave%20in%20the%20file%20only%20the%20records%20that%20are%20shown%20only%20once%20by%20default%20and%20eliminate%20all%20the%20others%20that%20are%20repeated.%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20David's%20name%20appears%20three%20times%20and%20Lesly's%20name%20appears%20once%2C%20delete%20David's%20records%20and%20keep%20only%20the%20Lesly%20record.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2951044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2951636%22%20slang%3D%22en-US%22%3ERe%3A%20Delete%20repeating%20records.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2951636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1214488%22%20target%3D%22_blank%22%3E%40Martha_C1100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20names%20are%20in%20A2%3AA1000.%3C%2FP%3E%0A%3CP%3EIn%20another%20column%2C%20enter%20the%20word%20Duplicate%20in%20row%201%2C%20and%20enter%20the%20following%20formula%20in%20row%202%3A%3C%2FP%3E%0A%3CP%3E%3DCOUNTIF(%24A%242%3A%24A%241000%2C%20%24A2)%26gt%3B1%3C%2FP%3E%0A%3CP%3EFill%20down%20from%20row%202%20to%20the%20last%20row%20with%20data%20(row%201000).%3C%2FP%3E%0A%3CP%3EThe%20new%20column%20will%20show%20TRUE%20for%20duplicate%20records%20and%20FALSE%20for%20unique%20ones.%3C%2FP%3E%0A%3CP%3EFilter%20the%20new%20column%20on%20TRUE.%3C%2FP%3E%0A%3CP%3EDelete%20the%20visible%20rows%20from%20row%202%20down%2C%20then%20remove%20the%20filter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello!
I would like to know how I can remove the duplicate data including the main one. that is, leave in the file only the records that are shown only once by default and eliminate all the others that are repeated.

For example, if David's name appears three times and Lesly's name appears once, delete David's records and keep only the Lesly record.

1 Reply

@Martha_C1100 

Let's say the names are in A2:A1000.

In another column, enter the word Duplicate in row 1, and enter the following formula in row 2:

=COUNTIF($A$2:$A$1000, $A2)>1

Fill down from row 2 to the last row with data (row 1000).

The new column will show TRUE for duplicate records and FALSE for unique ones.

Filter the new column on TRUE.

Delete the visible rows from row 2 down, then remove the filter.