Probabilities...

[proficient]

Hello everyone, I have a list from A to Z, what I want to do is to make a probability list of two alphabets, like AA, AB, AC when A series finishe I need to make B list then C, D till Z, all sets will be made in same colum after finishing previous alphbet sets... what formula will work here? Please advise.

 

[Eric W]

Several options:

Excel 2012

	A	B	C	D	E
1	AA	AA	AA		A
2	AB	AB	AB		B
3	AC	AC	AC		C
4	AD	AD	AD		D
5	AE	AE	AE		E
6	AF	AF	AF		F
7	AG	AG	AG		G
8	AH	AH	AH		H
9	AI	AI	AI		I
10	AJ	AJ	AJ		J
11	AK	AK	AK		K
12	AL	AL	AL		L
13	AM	AM	AM		M
14	AN	AN	AN		N
15	AO	AO	AO		O
16	AP	AP	AP		P
17	AQ	AQ	AQ		Q
18	AR	AR	AR		R
19	AS	AS	AS		S
20	AT	AT	AT		T
21	AU	AU	AU		U
22	AV	AV	AV		V
23	AW	AW	AW		W
24	AX	AX	AX		X
25	AY	AY	AY		Y
26	AZ	AZ	AZ		Z
27	BA	BA	BA
28	BB	BB	BB
29	BC	BC	BC
30	BD	BD	BD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A1 =CHAR(INT((ROW()-1)/26)+65)&CHAR(MOD(ROW()-1,26)+65) B1 =SUBSTITUTE(ADDRESS(1,ROW()+26,4),1,"") C1 =INDEX(E:E,INT((ROW()-1)/COUNTA(E:E))+1)&INDEX(E:E,MOD(ROW()-1,COUNTA(E:E))+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formula in A1 uses CHAR to get the A-Z list. The INT and MOD functions figure out from the ROW what values to use.

The formula in B1 uses the fact that Excel column addresses count up in the same way as you describe for a slightly shorter formula.

The formula in C1 doesn't assume you want a list of A, B, C, etc. but lets you put your list in column E.

 

[proficient]

Thank you so much, have one question, you used 26 cau there are 26 alphabets in English, what if we have no criteria then how can we use substitute and CHAR formulae...?

 

[proficient]

Moreover, my list starts from A3, how can I adjust references...? As in your given formula it covers the whole column...

 

[proficient]

One more question, what if I want to make AAA ABB then AAAA ABBB...?

 

[Eric W]

I don't know exactly how CHAR will work with other languages, so I'd recommend using the C1 formula. Just create a list of the characters you want to use in column E (or a column of your choice).

Also, that formula is designed to work from row 1. Look at the formula and you will see "ROW()-1" in 2 places. If you start in row 3, change that to "ROW()-3" in both places.

And finally, if you want to make longer lists of combinations, it is possible to do it with formulas, but it gets complicated fast. I'd recommend using a macro to calculate them. I wrote one up here:

https://www.mrexcel.com/forum/excel-questions/994820-list-all-possible-combinations.html

It has instructions on how to use it. You can make the characters you want in columns A, B, C, etc. You can put the same list in each column if you want to get the lists you ask for.

Hope this helps!