VBA to Search column for Duplicate on first 8 characters only

  • Hello everyone. (Newbie here).


    I'm looking for some help / guidance on how to search a column (Column D) for duplicate text based on only the first 8 characters and then delete all duplicate rows leaving only the last entry found.


    Column AColumn BColumn CColumn DColumn E
    C01*BRDBRADFORDQ-039962SC241219
    C01*BRDBRADFORDQ-039962ASC241219
    C01*BRDBRADFORDQ-039962BSC241219
    C01*BRDBRADFORDQ-039962CSC241219
    C01*BRDBRADFORDQ-039962DSC241219


    From the above table I would like to show only the below results.


    Column AColumn BColumn CColumn DColumn E
    C01*BRDBRADFORDQ-039962DSC241219


    Any help greatly appreciated :)

  • Hi Dave

    Thank for the reply.


    Column E might be duplicated elsewhere in the table but against as different account number Column A.


    Column D is the unique reference I need to remove apart from the SUFFIX letter, A B C etc. which gets added if there is a revision.


    I want to remove all revisions and leave only the last revision.


    So if Q-039903 and Q-033903A exist in the table only Q-033903A is shown.


    I've highlight in the excel file the results which should be the only result shown if there is a duplicate across the first 8 characters.


    Thanks

  • Probably need to be careful with this. It will find the last instance in the column, if you sort the sheet it will still find the last instance in the column.


    The code will find the last instance of a unique item and color it green, then it will loop and delete the rows that are not green.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!