A unique de-dupe challenge..

%3CLINGO-SUB%20id%3D%22lingo-sub-2871505%22%20slang%3D%22en-US%22%3EA%20unique%20de-dupe%20challenge..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871505%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Excel%20Community%2C%20I%20have%20a%20unique%20de-dupe%20challenge%3A%20I%20need%20to%20find%20all%20the%20duplicate%20values%20in%20the%20ID%20column%20and%20then%20look%20at%20the%20date%20column%20and%20remove%20all%20the%20duplicate%20rows%20EXCEPT%20the%20row%20with%20the%20most%20recent%20date.%26nbsp%3B%20So%20the%20end%20result%20will%20be%3A%20all%20unique%20values%20that%20were%20already%20there%20%2B%20the%20most%20recent%20dated%20duplicate%20values.%26nbsp%3B%20See%20the%20attached%20sample%20file%20and%20let%20me%20know%20your%20expert%20thoughts%20on%20the%20best%20script%20or%20formula%20approach%20that%20can%20accomplish%20this.%26nbsp%3B%20The%20actual%20spreadsheet%20will%20have%20over%201000%20rows%20-%20so%20I%20don't%20want%20to%20crash%20Excel%20either%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2871505%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%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871578%22%20slang%3D%22en-US%22%3ERe%3A%20A%20unique%20de-dupe%20challenge..%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1192449%22%20target%3D%22_blank%22%3E%40JohnManley%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EA%20relatively%20easy%20way%20to%20do%20this%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ESort%20the%20data%20by%20ID%20and%20Date%20(newest%20to%20oldest%20on%20the%20latter).%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0831.png%22%20style%3D%22width%3A%20586px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319083i88EDCD5E886B84D0%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0831.png%22%20alt%3D%22S0831.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EOn%20the%20Data%20tab%20of%20the%20ribbon%2C%20select%20Remove%20Duplicates.%3CBR%20%2F%3EClear%20the%20check%20boxes%20for%20all%20columns%20except%20ID%3CBR%20%2F%3EClick%20OK.%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0833.png%22%20style%3D%22width%3A%20436px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F319087i5CC8A2DCC1A0537F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0833.png%22%20alt%3D%22S0833.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E
New Contributor

Dear Excel Community, I have a unique de-dupe challenge: I need to find all the duplicate values in the ID column and then look at the date column and remove all the duplicate rows EXCEPT the row with the most recent date.  So the end result will be: all unique values that were already there + the most recent dated duplicate values.  See the attached sample file and let me know your expert thoughts on the best script or formula approach that can accomplish this.  The actual spreadsheet will have over 1000 rows - so I don't want to crash Excel either   

2 Replies

@JohnManley 

A relatively easy way to do this:

  1. Sort the data by ID and Date (newest to oldest on the latter).
    S0831.png
  2. On the Data tab of the ribbon, select Remove Duplicates.
    Clear the check boxes for all columns except ID
    Click OK.
    S0833.png

Worked perfectly.  @Hans Vogelaar, you're the man!  Thank you!