Removing Partial Duplicates and Keeping the Long String Value

Hi,

I am not sure if the title explains what I am trying to achieve but I want to find a formula that can look through the list of partial duplicates and extracting the longest string value. Attached is an example of what I am trying to achieve. If there is no formula that can do this, is there a vba code that can?

Thank you!

1. Remove Duplicates.

2. Try this in D2:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$9)/(COUNTIF($A$2:$A$9,$A$2:$A$9&"*")=1),ROWS($D$2:D2))),"")

Originally Posted by AliGW

Why are you discarding Shirt_170511_010000-JJ when it is longer than Shirt_170511_010000?

Will the longest version always come last in the original list, or could it appear anywhere?

My apologies AliGW, it should have been Shirt_170511_010000-JJ and not Shirt_010000. Thanks for pointing that out.

Originally Posted by Phuocam

1. Remove Duplicates.

2. Try this in D2:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$9)/(COUNTIF($A$2:$A$9,$A$2:$A$9&"*")=1),ROWS($D$2:D2))),"")

Thank you for the code Phuocam, so far it is working. Do you mind explain on how you thought of this code?

Thank you both so much for replying to this topic!