Ranking with a difference

[Mel Smith]

I am trying to write a formula that will help me to identify the "best" result and any help will be most appreciated.
Given the following sample data, the result I am trying to achieve is:

6\63, 4\41, 6\54, 4\34, 5\46 The "best" is 6\54 (to rank them would achieve: 6\54, 6\63, 5\46, 4\34, 4\41)

In other words, the first number if highest is the best, if there are two or more 'first numbers' the same, then the deciding factor if the lowest second number.

Mel

 

[Fluff]

If they will always be 1 number\2numbers, try

+Fluff v2.xlsm
	F	G
1
2	6/63	6/54
3	4/41	6/63
4	6/54	5/46
5	4/34	4/34
6	5/46	4/41
Main

Cell Formulas
Range	Formula
G2:G6	G2	=SORTBY(F2:F6,LEFT(F2:F6)+0,-1,RIGHT(F2:F6,2)+0,1)
Dynamic array formulas.

 

[Mel Smith]

=SORTBY(F2:F6,LEFT(F2:F6)+0,-1,RIGHT(F2:F6,2)+0,1)

Hi Fluff,

I put this formula into my spreadsheet: =SORTBY(D46:D53,LEFT(D49:D53)+0,-1,RIGHT(D49:D53,2)+0,1) and the result was #Value!

Have I missed something?

 

[Mel Smith]

Hi Fluff,

Finger trouble at my end. It works perfectly, of course!
Many thanks.
Mel

 

[Mel Smith]

Hi Fluff,

A followup question, if I may. How would I achieve the same result if the range was not contiguous? e.g. sample data in, say, G52, J47 and K25
Mel

 

[Fluff]

I've no idea I'm afraid.

 

[Mel Smith]

OK, thanks...

 

[Fluff]

You're welcome & thanks for the feedback.