Function to find the maximum in range considering the artificial hierarchy placed on prefixed letter values

[TonyChestnut]

Hi,

Hopefully the title of this thread makes some sense. Let me explain what I am trying to do...

I have a range of values, A1:A5. In this hypothetical situation say that they are the following:

A1 = P1
A2 = T1
A3 = C4
A4 = PC1
A5 = C6

The letter/s on the left relate to a hierarchy where P is the lowest, T is next, then C, and PC is the 'highest'.

What I need is a function (cannot be a macro, and not possible to use helper cells due to the extent of the project) that will return the 'highest' result, first considering the Letter/s on the left, and then the numbers on the right.

The function should return, in the given scenario, the value of A4. If that wasn't there then the value of A5.

I've been ripping my hair out trying various combinations of MAX, IF, SUBSTITUTE, LARGE, VALUE, N, LOOKUP, etc, but I fail everytime.

Please help me win.

Kind regards,

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum,

Question:
Is there only one digit after the letters on the left?
Or Cxx, Txx, PCxxx, Pxxx etc are also possible?

M.

 

[Marcol]

Another question, what version of Excel do you need this to work with?

 

[Marcol]

Try this array formula, confirmed with Ctrl+Shift+Enter not just Enter

=IFERROR(INDEX($A$1:$A$5,MATCH(LARGE(INDEX(INDEX(IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="P",100,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="T",200,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="C",300,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="PC",400,0)))),,1)+INDEX(RIGHT($A$1:$A$5,1)*1,,),,1),ROWS($1:1)),INDEX(INDEX(IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="P",100,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="T",200,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="C",300,IF(INDEX(LEFT($A$1:$A$5,LEN($A$1:$A$5)-1),,1)="PC",400,0)))),,1)+INDEX(RIGHT($A$1:$A$5,1)*1,,),,1),0)),"")

This seems to work for 2010 but is to large for 2003.

If it works for you it might be better using named ranges to simplify the formula.

 

[Marcol]

Hmm?
Create the following Names
1/. DataList (Dynamic. so your range can grow as required, no blanks, Subtract one from COUNTA if you have a header)

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

2/. IntegerList

=INDEX(RIGHT(DataList,1)*1,,)

3/. PrefixList

=INDEX(LEFT(DataList,LEN(DataList)-1),,1)

4/. Prefix_PC

=INDEX(IF(PrefixList="PC",400,0),,1)

5/. Prefix_C

=INDEX(IF(PrefixList="C",300,0),,1)

6/. Prefix_T

=INDEX(IF(PrefixList="T",200,0),,1)

7/. Prefix_P

=INDEX(IF(PrefixList="P",100,0),,1)

The formula now becomes ...

=INDEX(DataList,MATCH(LARGE(INDEX(Prefix_P+Prefix_T+Prefix_C+Prefix_PC+IntegerList,,1),ROWS($1:1)),INDEX(Prefix_P+Prefix_T+Prefix_C+Prefix_PC+IntegerList,,1),0))

Because all names are treated as arrays this can be confirmed with Enter.

You could of course combine more names to get an even simpler end formula.

This works in 2003 so it should be okay for 2007 and above

 

[Marcol]

Oops! Just noticed the integer part might be wrong!
Change it to this if it is
IntegerList

=INDEX(1/RIGHT(DataList,1),,)

The formula to get the max is

=INDEX(DataList,MATCH(MAX(INDEX(Prefix_P+Prefix_T+Prefix_C+Prefix_PC+IntegerList,,1)),INDEX(Prefix_P+Prefix_T+Prefix_C+Prefix_PC+IntegerList,,1),0))

 

[Marcol]

This might be another "Short Cut" Name
PrefixToValue
Refers to

=INDEX(LOOKUP(LEFT(DataList,LEN(DataList)-1),{"C","P","PC","T"},{300,100,400,200}),,1)

This replaces all the prefix Names

To get a sorted list, Drag/Fill Down

=INDEX(DataList,MATCH(LARGE(INDEX(PrefixToValue+IntegerList,,1),ROWS($1:1)),INDEX(PrefixToValue+IntegerList,,1),0))

To get the max

=INDEX(DataList,MATCH(MAX(INDEX(PrefixToValue+IntegerList,,1)),INDEX(PrefixToValue+IntegerList,,1),0))

Enough for now, I'll wait for some feedback, off to the pub ...

 

[TonyChestnut]

Hi and welcome to Mr Excel Forum,

Question:
Is there only one digit after the letters on the left?
Or Cxx, Txx, PCxxx, Pxxx etc are also possible?

M.

Hi, thanks for your question...Yes, often there will be more than one digit after the letters.

 

[TonyChestnut]

Another question, what version of Excel do you need this to work with?

Hi Marcol, I need it to work with 2007 and 2010, but if it works with 2003 that would be great too (but not essential). Thanks

 

[TonyChestnut]

Hi Marcol,

I tried the first formula you submitted, but it wouldn't work if there was a gap in the list. Unfortunately I need to be able to leave spaces in the list. Also it needs to work with multiple numbers on the right, for example C14, PC23, T112 etc.

Other than that it functions perfectly!

I'll look into your other replies now.

Thank you so much for your time.