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

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

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

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

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 -

Very cool solution!

In article
,
Pete_UK wrote:

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

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 -

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