Keeping only the latest revision in Excel-CodePudding

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.

CodePudding user response：

I know it deviates from your desired results, but I figured it would just be an intermediate step. So if available try:

Formula in C1:

=UNIQUE(XLOOKUP(LEFT(A1:A8,FIND("_",SUBSTITUTE(A1:A8,".","_"))-1)&"_*",SUBSTITUTE(A1:A8,".","_"),A1:A8,,2,-1))

Just for fun an alternative in older versions to get the Y or N values:

Formula in C1:

=IF(SUMPRODUCT(COUNTIF(A2,LEFT(A1,MIN(FIND({".","_"},A1&"_.")-1))&{".*","_*"})),"N","Y")

Or even:

=IF(COUNTIF(A2,LEFT(A1,MIN(FIND({".","_"},A1&"_.")-1))&"_*"),"N","Y")

Might work right?

CodePudding user response：

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.