Display all combination of letters
 

How do I display all combinations of a set of letters in row 1? The total of
numbers that are entered in row 1 can differ.

Example
Cell A1 = H Cell B1 = j Cell C1 =D

The following will be displayed in individual cells starting in Cell A2
A
A j
A j D
j D

Display all combination of letters
 

What about

j
D
A D

???


For your example:

A2: =A1
A3: =A1 & " " & B1
A4: =A1 & " " & B1 & " " & C1
A5: =B1 & " " & C1

Not sure what you mean by "the total of numbers that are entered in row
1 can differ" - aren't you entering letters?


In article ,
Mally wrote:

How do I display all combinations of a set of letters in row 1? The total of
numbers that are entered in row 1 can differ.

Example
Cell A1 = H Cell B1 = j Cell C1 =D

The following will be displayed in individual cells starting in Cell A2
A
A j
A j D
j D

Display all combination of letters
 

Hi JE

Thank you for noticing my mistake.

Simply, if I enter 3 letters in row 1 how can all of the combinations be
displayed automatically (without repeating ones e.g. ABC and CBA)

If I enter 7 letters in row 1 all of the combinations will be displayed

If I enter 10 letters in row 1 all of the combinations will be displayed

etc., etc.

"JE McGimpsey" wrote:

What about

j
D
A D

???


For your example:

A2: =A1
A3: =A1 & " " & B1
A4: =A1 & " " & B1 & " " & C1
A5: =B1 & " " & C1

Not sure what you mean by "the total of numbers that are entered in row
1 can differ" - aren't you entering letters?


In article ,
Mally wrote:

How do I display all combinations of a set of letters in row 1? The total of
numbers that are entered in row 1 can differ.

Example
Cell A1 = H Cell B1 = j Cell C1 =D

The following will be displayed in individual cells starting in Cell A2
A
A j
A j D
j D

Display all combination of letters
 

AFAIK, there's no really easy way to do this by formula, especially
since there's 127 combinations with 7 letters, and 1,023 combinations
with 10 letters.

You could, of course, brute force with 1023 formulae.

The alternatively would be to write a macro.


In article ,
Mally wrote:

Simply, if I enter 3 letters in row 1 how can all of the combinations be
displayed automatically (without repeating ones e.g. ABC and CBA)

If I enter 7 letters in row 1 all of the combinations will be displayed

If I enter 10 letters in row 1 all of the combinations will be displayed

Display all combination of letters
 

Here's a formula solution for up to 10 letters arranged contiguously
in row 1. Use A1 to J1 to enter your letters, and put this formula in
A2:

=IF(ROW(A1)=2^COUNTA(A$1:J$1),"",IF(MOD(INT(ROW(A 1)/1),2)=1,A$1,"")&IF
(MOD(INT(ROW(A1)/2),2)=1,B$1,"")&IF(MOD(INT(ROW(A1)/4),2)=1,C$1,"")&IF
(MOD(INT(ROW(A1)/8),2)=1,D$1,"")&IF(MOD(INT(ROW(A1)/16),2)=1,E$1,"")&IF
(MOD(INT(ROW(A1)/32),2)=1,F$1,"")&IF(MOD(INT(ROW(A1)/64),2)=1,G$1,"")
&IF(MOD(INT(ROW(A1)/128),2)=1,H$1,"")&IF(MOD(INT(ROW(A1)/256),2)=1,I
$1,"")&IF(MOD(INT(ROW(A1)/512),2)=1,J$1,""))

This is all one formula, probably easier to follow like this:

=IF(ROW(A1)=2^COUNTA(A$1:J$1),"",
IF(MOD(INT(ROW(A1)/1),2)=1,A$1,"")
& IF(MOD(INT(ROW(A1)/2),2)=1,B$1,"")
& IF(MOD(INT(ROW(A1)/4),2)=1,C$1,"")
& IF(MOD(INT(ROW(A1)/8),2)=1,D$1,"")
& IF(MOD(INT(ROW(A1)/16),2)=1,E$1,"")
& IF(MOD(INT(ROW(A1)/32),2)=1,F$1,"")
& IF(MOD(INT(ROW(A1)/64),2)=1,G$1,"")
& IF(MOD(INT(ROW(A1)/128),2)=1,H$1,"")
& IF(MOD(INT(ROW(A1)/256),2)=1,I$1,"")
& IF(MOD(INT(ROW(A1)/512),2)=1,J$1,""))

You will need to copy the formula down to A1024 to see all
combinations. If you put a b c in A1, B1 and C1, for example, you will
get this:

a
b
ab
c
ac
bc
abc

Now enter d in D1 and it will change to this:

a
b
ab
c
ac
bc
abc
d
ad
bd
abd
cd
acd
bcd
abcd

You can easily extend the formula to more than 10 letters if you
notice the symmetry of it.

I'm not sure if you are even monitoring this post any more, but a
pleasant way to pass a cold Saturday afternoon before the football
starts !! <bg

Hope this helps.

Pete



On Dec 30 2008, 9:36*am, Mally
wrote:
Hi JE

Thank you for noticing my mistake.

Simply, if I enter 3 letters in row 1 how can all of the combinations be
displayed automatically (without repeating ones e.g. ABC and CBA)

If I enter 7 letters in row 1 all of the combinations will be displayed

If I enter 10 letters in row 1 all of the combinations will be displayed

etc., etc.



"JE McGimpsey" wrote:
What about

* *j
* *D
* *A D

???

For your example:

A2: * =A1
A3: * =A1 & " " & B1
A4: * =A1 & " " & B1 & " " & C1
A5: * =B1 & " " & C1

Not sure what you mean by "the total of numbers that are entered in row
1 can differ" - aren't you entering letters?

In article ,
*Mally wrote:

How do I display all combinations of a set of letters in row 1? *The total of
numbers that are entered in row 1 can differ.

Example
Cell A1 = H * Cell B1 = j * Cell C1 =D

The following will be displayed in individual cells starting in Cell A2
A
A j
A j D
j D- Hide quoted text -

- Show quoted text -

Display all combination of letters
 

Very cool solution!

In article
,
Pete_UK wrote:

Here's a formula solution for up to 10 letters arranged contiguously

Display all combination of letters
 

Thanks, JE - it was your earlier comment that prompted me to have a go
<bg:

AFAIK, there's no really easy way to do this by formula,

Pete

On Jan 3, 5:51*pm, JE McGimpsey wrote:
Very cool solution!

In article
,



*Pete_UK wrote:
Here's a formula solution for up to 10 letters arranged contiguously- Hide quoted text -

- Show quoted text -

Display all combination of letters
 

In article
,
Pete_UK wrote:

it was your earlier comment that prompted me to have a go

And that's why I said it, of course.

You'd be surprised at how many things "I" get done that way... <g