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.
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
Ja wiem, że to jest proste w SQL-u – nawet o tym pisałem tutaj: http://xpil.eu/kombinacje/
Dowcip, żeby to samo zrobić w Excelu, bez SQL-a pod ręką.