[Solved] find and remove partial duplicates in excel

by


In an unused column to the right, put this array formula¹ in the second row.

=ISNUMBER(MATCH(LEFT(A2, MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9}, A2)-1, 1E+99)))&MID(A2, FIND(".", A2), 9),A:A, 0))

Finalize with CSE and fill down as necessary. Use Data ► AutoFilter to filter on that column for TRUE and delete the visible rows.

identify_duplicates

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

3

solved find and remove partial duplicates in excel