Assign a numeric value to random text for sorting

[sstronghill]

Calling all gurus. I'm attempting to create a self-sorting 'Standings'
table that uses a whole wack of lookup tables to sort my March Madness
pool by points standings, then alphabetically if two people are tied.
The problem is, how does excel know which player comes first? I've been
using a function that assigns a numeric value to the first few letters
of the persons name, but this often results in duplicate numeric values
(like if there's two John's in the pool) and get's really complicated
if someone wants to use a number (like 2Late) for their pool name.
Here's the formula I've been using so far (where A1 contains name):

=((CODE(LEFT(A1,1))-64) **first letter
+((CODE(MID(A1,2,1))-96)/100) **second letter /100 to offset for
weighting
+((CODE(MID(A1,3,1))-96)/10000) **third letter /10000
+((CODE(MID(A1,4,1))-96)/1000000) **fourth letter /1000000
+IF(LEN(A1)>4,((CODE(MID(A1,5,1))-96)/100000000),0)) **fifth letter,
if req'd
/100 **divide the whole thing by 100 to get a number between 0 and 1
+0.000000001 **added to duplicate names to ensure unique entries
(HACK!)

This gives me a unique number between 0 and 1 (for instance, George =
0.0705151807) that gets added to thier overall points rank (always an
integer) to give an overall ranking.

Problems abound! We need to know if it's upper or lower case to know
whether to subtract 64 or 96; if it's a number we shouldn't subtract
anything; what if there's a space or special character in the name?;
What if the name is less than 4 letters long?; What about duplicate
names???

I could keep adding error checking to the formula, but there's got to
be a better way!

Help!!
Scott

 

[Pete_UK]

To overcome some of these problems:

.... We need to know if it's upper or lower case to know whether to
subtract 64 or 96; if it's a number we shouldn't subtract anything;
what if there's a space or special character in the name?; What if the
name is less than 4 letters long?; What about duplicate names??? ...

you could impose naming conventions, eg no spaces allowed, first
character must be between A and Z, names must be at least 4 characters
in length, no duplicate names (eg the second John becomes John2) etc.
and your formula could cope with upper case and lower case letters by
means of the LOWER( ) function applied to A1.

You could set up a User Defined Function to do the transformation -
this would allow you to test for the length of the name and for other
special characters which might be allowed, such as _ or @ or * and
numbers, and you could incorporate more extensive error checking.

Hope this helps.

Pete

 

[bplumhoff]

Hello Scott,

The CODE/MID/etc-solution is quite limited, I am afraid.

I suggest to change your approach completely:

If your text resides in column A, write into B1:
=COUNTIF($A:$A,"<"&A1)+COUNTIF($A$1:A1,A1)

And into C1:
=INDEX(A:A,MATCH(ROW(),B:B,FALSE))

Copy B1:C1 down ...

HTH,
Bernd

 

[sstronghill]

THANK YOU BERND!!!!

This is exactly the slick solution I was looking for. Works like a
charm!!

My solution was not only limited, but cumbersome and fraught with
problems.

Thanks again. A solution and an education in one!!!
Scott