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.