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
Dodaj komentarz

avatar
2 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
2 Comment authors
adminButter Recent comment authors
  Subscribe  
najnowszy najstarszy oceniany
Powiadom o
Butter
Gość
Butter

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

%d bloggers like this: