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,

 

[TonyChestnut]

Hi Marcol,

I have tried your other suggestion using the DataList, PrefixToValue, and Integer names.

I couldn't quite get it to function properly (probably my lack of experience), but also I'd like to steer clear of using helper cells because I will eventually have several hundred instances of this formula on a single worksheet.

Any further advice would be great.

Many thanks

 

[Marcol]

See if this workbook on my SkyDrive helps, it works for 2003 and later.
Sort By Artificial Hierarchy

This uses dynamic named ranges (See Formulas > Name Manager)
1/. DataList, this allows for blanks, and formatting errors.
Refers to:

=UPPER(Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A,1)))

2/. IntegerList This handles up to 999 rows, increase the divisor if more are required e.g. /!0^6

=INDEX(IF(DataList="","0",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DataList,"PC",""),"C",""),"T",""),"P",""))[COLOR=#ff0000]/1000[/COLOR],,1)

3/. PrefixList, Change the multiplier to match IntegerList divisor if required

=INDEX(IF(DataList="","",LEFT(DataList,LEN(DataList)-LEN(IntegerList[COLOR=#ff0000]*1000[/COLOR]))),,1)

4/. PrefixToValues

=INDEX(IF(DataList="",0,LOOKUP(PrefixList,{"C","P","PC","T"},{300,100,400,200})),,1)

Then in B2, Drag/Fill Down. This returns a list sorted according to your hierarchy.

=IF(ROWS($2:2)>COUNTA(DataList),"",INDEX(DataList,MATCH(LARGE(INDEX(PrefixToValue+IntegerList,,1),ROWS($2:2)),INDEX(PrefixToValue+IntegerList,,1),0),0))

This list is for reference only, and is not required if you only need to find your weighted max value.

If you only require the maximum according to your heirarchy ..
Then in B2

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

If you don't use these names the formulae will become horrifically complicated and involve array entry.
These expanded formula will not be acceptable in 2003.

 

[TonyChestnut]

Hi Marcol,

It works perfectly, and I've learned something valuable in dynamic named ranges.

One last thing...how do I adopt this so that I can have multiple 'data strings' (and a 'max' for each) on the same sheet? It seems that the Datalist is referenced in the majority of the dynamic named ranges, and as such may cause problems.

Kind regards,

 

[barry houdini]

Hello Tony,

It looks like Marcol has provided you with a good solution but here's what I came up with FWIW......

Assuming data in A1:A100, possibly with blanks, this formula will give you the "highest" value in A1:A100, assuming up to 3 digits

=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&MATCH(2,1/MMULT((TRANSPOSE(A1:A100)=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&ROW(INDIRECT("1:999")))+0,ROW(A1:A100)^0))

confirmed with CTRL+SHIFT+ENTER

 

[TonyChestnut]

=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&MATCH(2,1/MMULT((TRANSPOSE(A1:A100)=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&ROW(INDIRECT("1:999")))+0,ROW(A1:A100)^0))

Mr Houdini, that is magic!


Big thanks to you and Marcol for making my day.

 

[Marcol]

Nice one Barry, I didn't think it could be done in a oner for 2003!
It will be interesting to see how a static volatile array performs against using dynamic names on larger sheets.

@ Tony
I don't understand this ...

...how do I adopt this so that I can have multiple 'data strings' (and a 'max' for each) on the same sheet?

Can you post an example of what you mean, or explain further.

 

[Marcelo Branco]

Another possible solution

A B C D

[TABLE="width: 235"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl66, width: 64, bgcolor: #c5d9f1"]Values[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]---------[/TD]
[TD="class: xl66, width: 121, bgcolor: #c5d9f1"]HighestHierachy[/TD]
[TD="class: xl66, width: 64, bgcolor: #c5d9f1"]Max[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]T1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"]PC[/TD]
[TD="class: xl65, bgcolor: transparent"]PC1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C44[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C456[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]T4[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]C999[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]T5[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]P45[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]P12[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]PC1[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]P1200[/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

Formula in C2
=INDEX({"PC";"C";"T";"P"},MATCH(TRUE,COUNTIF(A:A,{"PC";"C";"T";"P"}&"*")>0,0))

Array formula in D2
=C2&MAX(IF(ISNUMBER(1*SUBSTITUTE($A$2:$A$11,C2,"")),1*SUBSTITUTE($A$2:$A$11,C2,"")))

confirmed with Ctrl+Shift+Enter

M.

 

[Marcol]

I'm loosing the plot here, which one of these tables is correctly sorted?

Excel Workbook
	A	B	C	D	E	F
1	Data Strings	Sorted	Max	Alternative >	Sorted	Max
2		PC30	PC30		PC23456789	PC23456789
3	T1	PC23456789			PC30
4	C45	PC1			PC023
5	PC1	PC023			PC1
6	c6	C6			C45
7	PC23456789	C45			C6
8	PC023	T1			T1
9	pc30
10
Alternatives

 

[TonyChestnut]

=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&MATCH(2,1/MMULT((TRANSPOSE(A1:A100)=LOOKUP(2,1/COUNTIF(A1:A100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&ROW(INDIRECT("1:999")))+0,ROW(A1:A100)^0))

Barry Houdini,

Please can you help one last time...I need the range to be F28:F100, but I don't know how to change it. (I've tried replacing the ranges, and replacing ROW with COLUMN, but alas the formula is just too technical for me).

Sorry to be a pain.

 

[barry houdini]

My suggested formula should work with any column of data, so you only have to replace all instances of A1:A100 with F28:F100 - everything else should remain as it is, i.e.

=LOOKUP(2,1/COUNTIF(F28:F100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&MATCH(2,1/MMULT((TRANSPOSE(F28:F100)=LOOKUP(2,1/COUNTIF(F28:F100,{"P","T","C","PC"}&"*"),{"P","T","C","PC"})&ROW(INDIRECT("1:999")))+0,ROW(F28:F100)^0))