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..)

 

[Jubjab]

You can use the formula CODE to get a number for a letter.

I.e.
CODE("A") = 65
CODE("B") = 66
and so on

note, however, that lowercap letters have higher numbers, i.e.

CODE("a") = 97
CODE("b") = 98

so it's worth converting all to either upper or lower cap:


So you might use this for sorting, i.e. if you in A1 have the word "Abracadabra", then you would use the formula

=CODE(UPPER(LEFT(A1,1)))

 

[oldbrewer]

let data be bb
cc
aa

copy this to somehere and sort it into order with order numbers so it looks like

aa 1
bb 2
cc 3

highlight this and insert, name, define, myrange

then vlookup (A1, myrange,2) (assuming bb is in A1) returns 2 so you can eg concatenate that 2 to the start of bb (making 2bb)

then left(cellref,1) is your ranking

 

[Bookender]

Thank you, Jubjab, that looks like it's the best method for me to apply so far. I wasn't even aware that command existed in Excel, but, one keeps learning.


Oldbrewer - I am not entirely sure what you're doing. Making a reference sheet for any combinations of the first three letters and simply doing lookup values based on those, or..?

 

[Bookender]

Maybe I've just been at this too long and my brain has stopped seeing the obvious solutions.

As some of my entries have the same first four letters, but still need to be sorted so that all entries with ABCD are sorted together, without, say, ACGD appearing in the middle. So, I have to look up the first four letters in each entry (or the first, third, fifth and tenth letter; something).

That has now resulted in a neat-looking 4-column helper spreadsheet with a bunch of numbers, but... I am a bit stuck on how the heck I can progress further and get these numbers into some kind of logical order that would ultimately allow me to simply plot in the cells by their alphabetic value.

The actual plotting will be simple enough; I just need to figure out a way to get the numbers into a logical order without having to use all 100k+ rows in Excel for a lookup...

And, for the record, I can use as many helper columns as needed to get this to work - my main task is to get rid of the additional 20-second lag from the existing array, and formulas are just a bit easier to optimize than arrays.

 

[Bookender]

Welp, after sleeping on it, I realised I could just use a SMALL function along with a bit of INDEX-MATCH to get things lined up neatly.

Now my problem is that Excel insists on trunctating my numbers, making it outright impossible to check if certain combinations have the same code and thus should be alphabetised together. The issues of having a third of the cells begin with "Management Issues:" and half of the rest with "Management Issued Recalls", with everything else having names between "Mop" and "Manly zebra-striped couch pillows"...

 

[Bookender]

(Sorry for the triple post, but the short edit-window on here sometimes makes it difficult to catch something in time to correct my last reply)


It struck me that someone might merely suggest that I look for spaces, and thus only check the first letter of every word to obtain unique entries. While this would work, the issue is that I have to check FIVE words. And while Excel will grudgingly manage those numbers as long as the number code isn't longer than 2 digits per letter, then it throws a gasket and trunctates my numbers the moment I have two three-digit values among the five.

And, currently, I have a huge problem with a set of letter-combinations that all equate "80109114457XX", where XX is the only variable between them, and Excel happily trunctates everything to 8,01091E+11 - or 8010911445800... And at the same time, I have other columns that merely state 67, or has the variable number being in the middle of the code, so I have no idea how I can do this alphabetising without having to do so manually.

Help me come up with a solution, please?

 

[Bookender]

After rambling at an unfortunate coworker for a bit, they suggested the brilliant solution of simply substracting 35 from the CODE values, thereby resulting in values that are low enough for Excel to accept.

Now I have found out that I need the letter-input from at least SEVEN words, due to the way that this system is set up.

I had hoped that it would be enough to alphabetise two columns out of my data spread (four from the first, one from the second), but it turns out that I need data from four of them to obtain unique enough values that I can use them for proper alphabetisation.

Seriously, any kind of ideas would be a tremendous help, as I have the feeling I spend most of my time arguing with myself and coming up with solutions in that manner...

 

[oldbrewer]

[TABLE="width: 711"]
<colgroup><col span="3"><col><col><col><col><col><col span="5"></colgroup><tbody>[TR]
[TD]aaa1[/TD]
[TD="align: right"]1[/TD]
[TD]<<<<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aaa1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaa11[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aaa11[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaaa1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aaaa1[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="colspan: 4"]same words put in order by excel[/TD]
[/TR]
[TR]
[TD]aaaa2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aaaa2[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="colspan: 2"]sort function[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abaa1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]aaaaaa1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abaaa1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]abaa1[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="colspan: 3"]this table is called mytable[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bbbb1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]abaaa1[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bbbb2[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bbbb1[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bbbb3[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bbbb2[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaaaaa1[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]bbbb3[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]formula in marked cell (B1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]=VLOOKUP(A1,mytable,2)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

I put the above up to grab your attention. I do not understand what you are trying to do. Please explain using only 3 of your words or letter combinations.