Pick highest numerical version in a List

[indy1982]

Hello,

I have the data below in Column A, each reference is a different cell going down in the column A1,A2,A3,A4 etc.

I would like a formula that picks out the bold references in the list below, so the highest number in the sequence using the last 2 or 3 numbers in the reference example below (851055TSAPPLE44). The number of characters in the cell could change, but I always want to pick out the 'highest version' by looking at the last 2 or 3 numerical digits (could be more) at the end.

Any help would be much appreciated. I have tried index and match formula to no avail.Thank you.


851055TSAPPLE35
851055TSAPPLE42
851055TSAPPLE43
851055TSAPPLE44
909011TSAPPLE40
909011TSAPPLE48
909011TSAPPLE49
1003983TSAPPLE153
1003983TSAPPLE167
1003983TSAPPLE168
1003983TSAPPLE170

 

[awoohaw]

what does "by looking at the last 2 or 3 numerical digits (could be more)" the could be more mean? It is a pretty ambiguous question you are asking.

 

[indy1982]

Sorry I should be more clear - it means looking at the last numerial digits in the cell example here is 2 digits 851055TSAPPLE44 but it could be a 3 digit number example 1003983TSAPPLE170

 

[awoohaw]

Sorry I should be more clear - it means looking at the last numerial digits in the cell example here is 2 digits 851055TSAPPLE44 but it could be a 3 digit number example 1003983TSAPPLE170

okay, what version of Excel are you using?

 

[indy1982]

Microsoft Excel for Office 365

 

[awoohaw]

Okay, try this:

mr excel questions 14.xlsm
	A	B	C	D
1
2	851055TSAPPLE35	35	170
3	851055TSAPPLE42	42
4	851055TSAPPLE43	43
5	851055TSAPPLE44	44
6	909011TSAPPLE40	40
7	909011TSAPPLE48	48
8	909011TSAPPLE49	49
9	1003983TSAPPLE153	153
10	1003983TSAPPLE167	167
11	1003983TSAPPLE168	168
12	1003983TSAPPLE170	170
13
Sheet6

Cell Formulas
Range	Formula
B2:B12	B2	=IF(ISNUMBER(1*RIGHT(A2:A12,3))=FALSE,RIGHT(A2:A12,2)*1,RIGHT(A2:A12,3)*1)
C2	C2	=MAX(IF(ISNUMBER(1*RIGHT(A2:A12,3))=FALSE,RIGHT(A2:A12,2)*1,RIGHT(A2:A12,3)*1))
Dynamic array formulas.

 

[indy1982]

Thank you .

I think the problem I will face here is then filtering Column B to say out of the 4 values in cells A2:A5 , 851055TSAPPLE44 is the value I want to pick before moving on to the next one C6:C8.

 

[awoohaw]

I just did the max on the total. I don't see any consistency in how you go down the column to determine which series to check for the maximum.
how did you choose those records with the number highlighted? Is there other data we don't see?

 

[indy1982]

The consistency is everything before the numerical value at the end has to match

So for cells A2:A5 I would select A5

The consistent part here is 851055TSAPPLE but I want 851055TSAPPLE44

 

[awoohaw]

are the letters in each value always "TSAPPLE"? Or always begin with "T"?