The Ultimate Solution

https://xpil.eu/Z3zWP

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.

https://xpil.eu/Z3zWP

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.