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.

2 komentarze

  1. WITH CTE AS (
    SELECT 65 X, CHAR(65) ZN
    UNION ALL
    SELECT X+1,CHAR(X+1) FROM CTE
    WHERE X<90)

    SELECT C1.ZN , C2.ZN , C3.ZN
    FROM CTE C1, CTE C2, CTE C3
    ORDER BY C1.ZN ,C2.ZN, C3.ZN

Leave a Comment

Komentarze mile widziane.

Jeżeli chcesz do komentarza wstawić kod, użyj składni:
[code]
tutaj wstaw swój kod
[/code]

Jeżeli zrobisz literówkę lub zmienisz zdanie, możesz edytować komentarz po jego zatwierdzeniu.