Display all combination of letters

[Mally]

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

 

[JE McGimpsey]

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?

 

[Mally]

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]

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.

 

[Pete_UK]

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(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,""))

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

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.

What about

   j
   D
   A D

For your example:

 

[JE McGimpsey]

Very cool solution!

 

[Pete_UK]

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

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

Pete

 

[JE McGimpsey]

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>