The Ultimate Solution

In my last two articles I explained how to generate all 3-letter combinations, starting from AAA, AAB, AAC (and so on) up to ZZX, ZZY, ZZZ.

The first solution was VBA-based; it consisted of three simple nested loops. The other one (posted yesterday) was purely formula-based but required using as much as four columns to achieve the same effect.

By the way, both articles were written in Polish – you are of course free to read them if you dare… Or if you speak that hissing, messy language.

Today I am going to show an ultimate solution of the problem. The solution I am going to present is also purely formula-based but, unlike the yesterday’s one, it consists of one formula only.

Type the following formula into the A1 cell and then copy-paste it down to the cell A17576.

=CHAR(65+INT(MOD(ROW(A1)-1,26*26*26)/(26*26)))&CHAR(65+INT(MOD(ROW(A1)-1,26*26)/26))&CHAR(MOD(ROW(A1)-1,26)+65)

Done!

Final conclusion: in this case, VBA approach seems to be the easiest, most readable and elegant one, despite the fact that many people prefer not to use scripting in favor of “pure” formula-based solutions. For me personally, VBA scripting is the most natural way of solving this type of problems. But, as always, it is up to you which way you choose.


Liczba słów w tym wpisie: 221

Sprawdź też

How to speed up WordPress blog: REDIS

About the Redis caching engine.

Supersedes

What happens when the same word means two different things in two languages.

Zapisz się
Powiadom o
guest
2 komentarzy
Inline Feedbacks
Zobacz wszystkie komentarze
2
0
Zapraszam do skomentowania wpisu.x
()
x