CODEWITHSUNDEEP
excelexcel-formula
user2861089
user2861089

Reputation: 1245

Using excel find all possible combinations in five columns

Using Excel, I want to find all possible combinations of 5 letters:

Say, in columns A-E I have the letters: a or A; b or B; c or C; d or D; e or E.

In column F, I want to display each combination. For example:

                       Combinations
a   b   c   d   e         abcde
A   B   C   D   E         Abcde
                          aBcde
                          abCde

Upvotes: 0

Views: 6735

Answers (2)

Excel Hero
Excel Hero

Reputation: 14764

Select the range F1:F32.

Paste the following formula into the Formula Bar:

=CHAR(CODE("a")-32*MID(DEC2BIN(ROWS($1:1)-1,5),1,1)) &
 CHAR(CODE("b")-32*MID(DEC2BIN(ROWS($1:1)-1,5),2,1)) &
 CHAR(CODE("c")-32*MID(DEC2BIN(ROWS($1:1)-1,5),3,1)) &
 CHAR(CODE("d")-32*MID(DEC2BIN(ROWS($1:1)-1,5),4,1)) &
 CHAR(CODE("e")-32*MID(DEC2BIN(ROWS($1:1)-1,5),5,1))

This is NOT an array formula, but since multiple cells are selected, please confirm the formula with Ctrl+Enter.

Upvotes: 2

user4084964
user4084964

Reputation:

Well my off the bat solution would increment an integer from 0 to 32. Look at the first 5 bits of the binary of the integer. If the first bit is 0, use the first row for first letter. If 1, second row. Similar for the rest of the letters. The concatenation of these will give your combinations.

Upvotes: 0

Related Questions