I have some text with revisions and some without revision in excel. I want to only keep the revision with the highest number. For example in the sample picture below. I want to include "002001" as it is unique but in case of "002050" I have 2 set of data one without the revision and one with the revision, in this case I want to keep "002050_R02" since it’s the highest revision. How do I accomplish this in excel without the VBA.
>Solution :
Sort column A.
Then use:
=IF(A3<>"",IF(LEFT(A2,MIN(FIND({".","_"},A2&"_."))-1)=LEFT(A3,MIN(FIND({".","_"},A3&"_."))-1),"N","Y"),"Y")
And copy down.
