Probabilities...

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
745
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Several options:

Excel 2012
ABCDE
AAAAAAA
ABABABB
ACACACC
ADADADD
AEAEAEE
AFAFAFF
AGAGAGG
AHAHAHH
AIAIAII
AJAJAJJ
AKAKAKK
ALALALL
AMAMAMM
ANANANN
AOAOAOO
APAPAPP
AQAQAQQ
ARARARR
ASASASS
ATATATT
AUAUAUU
AVAVAVV
AWAWAWW
AXAXAXX
AYAYAYY
AZAZAZZ
BABABA
BBBBBB
BCBCBC
BDBDBD

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"][/TD]

[TD="align: center"]23[/TD]

[TD="align: right"][/TD]

[TD="align: center"]24[/TD]

[TD="align: right"][/TD]

[TD="align: center"]25[/TD]

[TD="align: right"][/TD]

[TD="align: center"]26[/TD]

[TD="align: right"][/TD]

[TD="align: center"]27[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A1[/TH]
[TD="align: left"]=CHAR(INT((ROW()-1)/26)+65)&CHAR(MOD(ROW()-1,26)+65)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=SUBSTITUTE(ADDRESS(1,ROW()+26,4),1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=INDEX(E:E,INT((ROW()-1)/COUNTA(E:E))+1)&INDEX(E:E,MOD(ROW()-1,COUNTA(E:E))+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.
 
Upvote 0
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...?
 
Upvote 0
Moreover, my list starts from A3, how can I adjust references...? As in your given formula it covers the whole column...
 
Upvote 0
One more question, what if I want to make AAA ABB then AAAA ABBB...?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top