Automatic alphabetization without array or VBA?

[Bookender]

TL;DR version:
Can I use some kind of formula to convert a letter into a number, without using 28+ IF nestlings?




Not too long; did read version:

I have a bit of a problem. Namely, I have a quite large spreadsheet that has a total of 1 array formula in it. But, that array seems to be pulling teeth when it puts things in alphabetic order (and yes, that has to be automatically done)...

So, is there some way of numbering things by their alphabetic position with the use of formulas? Then I could use Offset/Match/Index or similar to get the entries ordered into their proper positions. But, I have no idea on how to check for just the first letter of a cell without having to do a IF() command for every single letter combination - needless to say, that means I run out of IF-nestlings ridiculously fast...

And I cannot use VBA for this, either. Blame the security settings on my computer.




(also, please note: The last three times someone said 'Nope, that's impossible' off the bat, it turned out that they just refused to think a bit out of the box, and that a solution could indeed be found with a bit of tinkering..)

 

[Bookender]

Examples:
"Intelligent Design: Neurological Studies In Single-Cell Animals" x6
"Intelligent Design: Neurological Studies in Mammals" x2
"Investigational Drossiers Narrating Several Introspective Solutions for Renal Failure". x7

My issue is that I have a column's worth of data. This data needs to be sorted alphabetically, as it is inputted in a completely random order, and the cells need to be sorted so that they are grouped together - all of the Intelligent Design: Mammal cells need to be together, all of the Investigational Drossiers cells need to be together, etc.

My issue is to find a way to assign each of these 'groups' a unique ID based on their contents. And due to the sheer quantity of cells, I have found that the only way I can obtain a truly unique ID for each of the groups is by looking up seven different values. An impossible task to do with CODE, as Excel trunctates any number with more than eleven digits, and I cannot do a SMALL sort on text strings.

It is important to note that the 15 examples would appear in completely random order, and the code would still be able to apply the same ID to any cell that happens to have the same contents. I cannot, at any point, do manual sorting. The code should be 100% fully automated once it has been created, and will automatically take the random inputs and plot them together correctly. So Sort is entirely and completely out of the picture, as that requires me to do manual work on the numbers. As are arrays, as a 4000-cell array takes far too long to compute. And thanks to security settings, I cannot work in VBA. So, I have to do this with formulas.

 

[Bookender]

Apparently, I had to sit down and think about this in a new way.

After the realisation that this does not need to be alphabetised, but merely needs to be sorted into groups, I realised that I could use a combination of a unique-finding COUNTIF formula; one that counts whether there's a unique entry and does a MAX(x:$x)+1 formula; a static helper column to supply me with static numbers; an INDEX/MATCH column to get all of the unique entries together by the static number; and finally another INDEX/MATCH that plugs in the static ID number based on what the location is within the unique-collected-entries part.

Then simply gather all of those and do another INDEX/MATCH for the entire column instead of only the unique ones.

So, yeah, that's a total of 5 helper columns and one result column. But it works like a charm, and, especially, does its job FAST. Doesn't alphabetise, but it does group things. Which, really, is all I need.

Thanks for the help, Jubjab and Oldbrewer; even if your suggestions were not the solution I needed, then you did help steer my head into what I could use instead.