Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

I have a spreadsheet that has value that look like this:

Row 1, 5 columns:: bbContag bbED bbENN_MN bbLPI bbLSI

I need to create a list from these numbers that always contain the value from column1 and then all possible combination of the values from the remaining columns' values, to produce an output like this:

bbContag+bbED+bbENN_MN+bbLPI+bbLSI

bbContag+bbED+bbENN_MN+bbLPI

bbContag+bbED+bbENN_MN

bbContag+bbED

bbContag+bbENN_MN+bbLPI+bbLSI

bbContag+bbENN_MN+bbLPI

bbContag+bbENN_MN

bbContag+bbLPI+bbLSI

bbContag+bbLPI

bbContag+bbLSI

etc, obviously this is not finished.

The output can remain in excel or output to a text file.

The maximum amount of columns with values will be 9, with a minimum of 2. I am looking for a script or macro to do this. Please be advise I currently do not have the wherewithal to do this myself, and would appreciated annotated code so I can begin to learn whats going on. I have attached a spreadsheet that is a sample of what I would like to use a script / macro on.

Thanks,

JE

If I knew what I was doing I might be able to use some of these suggestions, but I don't and that's why I am calling on the good graces of the EE experts:

http://www.ozgrid.com/forum/showthread.php?t=40004

http://www.excelforum.com/excel-programming/632749-list-all-the-combinations-of-a-group-of-cells-containing-letters-but-not-numbers.html

je-combo.xls

Row 1, 5 columns:: bbContag bbED bbENN_MN bbLPI bbLSI

I need to create a list from these numbers that always contain the value from column1 and then all possible combination of the values from the remaining columns' values, to produce an output like this:

bbContag+bbED+bbENN_MN+bbL

bbContag+bbED+bbENN_MN+bbL

bbContag+bbED+bbENN_MN

bbContag+bbED

bbContag+bbENN_MN+bbLPI+bb

bbContag+bbENN_MN+bbLPI

bbContag+bbENN_MN

bbContag+bbLPI+bbLSI

bbContag+bbLPI

bbContag+bbLSI

etc, obviously this is not finished.

The output can remain in excel or output to a text file.

The maximum amount of columns with values will be 9, with a minimum of 2. I am looking for a script or macro to do this. Please be advise I currently do not have the wherewithal to do this myself, and would appreciated annotated code so I can begin to learn whats going on. I have attached a spreadsheet that is a sample of what I would like to use a script / macro on.

Thanks,

JE

If I knew what I was doing I might be able to use some of these suggestions, but I don't and that's why I am calling on the good graces of the EE experts:

http://www.ozgrid.com/forum/showthread.php?t=40004

http://www.excelforum.com/excel-programming/632749-list-all-the-combinations-of-a-group-of-cells-containing-letters-but-not-numbers.html

je-combo.xls

=$A$1 & IF(MOD(ROW(),2)>=1,"+" & B$1,"")& IF(MOD(ROW(),4)>=2,"+" & C$1,"")& IF(MOD(ROW(),8)>=4,"+" & D$1,"")& IF(MOD(ROW(),16)>=8,"+" & E$1,"")

If you copy this formula down, it will build the requested string. I trust you can generalize the pattern for different numbers of words.

Brad

Thanks. So what do I change if I have more than 5 columns?

Also this includes the value from column A plus itself (i.e. bbContag + bbContag) is there way to make it not do that? It didn't quite work please review the output below (repeats, etc):

bbContag+bbED

bbContag+bbContag

bbContag+bbED+bbContag

bbContag+bbED

bbContag+bbED+bbED

bbContag+bbContag+bbED

bbContag+bbED+bbContag+bbE

bbContag+bbLSI

bbContag+bbED+bbLSI

bbContag+bbContag+bbLSI

bbContag+bbED+bbContag+bbL

bbContag+bbED+bbLSI

bbContag+bbED+bbED+bbLSI

bbContag+bbContag+bbED+bbL

bbContag+bbED+bbContag+bbE

bbContag

Thanks again,

Cheers,

JE

Having the row values in the list, you can get the permutations with all the elements from the row using the code like this:

import itertools

row = ['bbContag', 'bbED', 'bbENN_MN', 'bbLPI', 'bbLSI']

for t in itertools.permutations(row

print '+'.join(t) # join the strings using the '+' or process somehow else

When you also want permutations of smaller lengths, just use the second argument itertools.permutations(row

```
import itertools
row = ['bbContag', 'bbED', 'bbENN_MN', 'bbLPI', 'bbLSI']
# If you want to do also permutations from using not all
# values in the list, then you can add the second argument
# of the itertools.permutations(row, n) and loop for n
# from 1 to n inclusively.
for n in range(1, len(row)+1):
print '\nPermutations of the length', n
print '-' * 50
for t in itertools.permutations(row, n):
print '+'.join(t)
```

result.txt
Sorry for the attached result.txt above. It contains also one more section at the beginning that contains the permutations made of all elements (i.e. from the simpler example typed in front of the snippet). The snippet results start with...

Permutations of the length 1

--------------------------

bbContag

bbED

bbENN_MN

bbLPI

bbLSI

Permutations of the length 2

--------------------------

bbContag+bbED

bbContag+bbENN_MN

bbContag+bbLPI

bbContag+bbLSI

bbED+bbContag

bbED+bbENN_MN

bbED+bbLPI

bbED+bbLSI

bbENN_MN+bbContag

bbENN_MN+bbED

bbENN_MN+bbLPI

bbENN_MN+bbLSI

bbLPI+bbContag

bbLPI+bbED

bbLPI+bbENN_MN

bbLPI+bbLSI

bbLSI+bbContag

bbLSI+bbED

bbLSI+bbENN_MN

bbLSI+bbLPI

Permutations of the length 3

--------------------------

bbContag+bbED+bbENN_MN

bbContag+bbED+bbLPI

bbContag+bbED+bbLSI

bbContag+bbENN_MN+bbED

bbContag+bbENN_MN+bbLPI

etc.

There are only 15 possible answers for four words in addition to the fixed one. If you put the formula in row 1 and copy down through row 15, all are listed. The invariant word is listed in row 16, and duplicates in the following rows.

As an alternative, here is a macro that will enumerate the possibilities in a column immediately below your words. Select the words, then run the macro.

To install a sub in a regular module sheet:

1) ALT + F11 to open the VBA Editor

2) Use the Insert...Module menu item to create a blank module sheet

3) Paste the suggested code in this module sheet

4) ALT + F11 to return to the spreadsheet

To run a sub or macro:

5) ALT + F8 to open the macro window

6) Select the macro

7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:

8) Repeat steps 5 & 6, then press the "Options" button

9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)

10) Enter some descriptive text telling what the macro does in the "Description" field

11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

```
Sub Combinatrix()
'Forms all the combinations of one fixed value with varying numbers of other values
'Uses binary counting method
Dim i As Long, j As Long, k As Long, N As Long, NN As Long
Dim v As Variant, vInputs As Variant, vResults As Variant
Dim s As String
N = Application.CountA(Selection.Value)
ReDim vInputs(1 To N)
For i = 1 To N
If Selection.Cells(i) <> "" Then
j = j + 1
vInputs(j) = Selection.Cells(i)
End If
Next
ReDim Preserve vInputs(1 To j)
NN = 2 ^ (N - 1) - 1
ReDim vResults(1 To NN, 1 To 1)
For i = 1 To NN
vResults(i, 1) = vInputs(1)
For j = 2 To N
k = IIf((i Mod 2 ^ (j - 1)) >= 2 ^ (j - 2), 1, 0)
If k = 1 Then vResults(i, 1) = vResults(i, 1) & "+" & vInputs(j)
Next
Next
Selection.Offset(1, 0).Resize(NN, 1).Value = vResults
End Sub
```

Also, from above I asked "I need to create a list from these numbers that always contain the value from column1 and then all possible combination of the values from the remaining columns' values" The python script does not do this, as is. Can this be implemented or would it be easier to go through and select the ones that fit this manually?

Also, can this be set up to work with a list of lists? What I mean is if you look at my spreadsheet example I have several rows I'd like to do this on, but each has different string values for among the cells in its row?

Thanks,

JE

This macro works well. My only question is, can it somehow place the result in a new sheet, or somewhere else.

The way my values are setup now I several rows with values that are right after one another. If you look at my example xls above you'll see what I mean. With the current incarnation of this macro it will replace the first column for the subsequent rows.

So, I guess, is there way to make it run all the rows and then save them as columns (e.g. row1's result = column a, row2's result = columnb, etc) on a new sheet? If this is not easily done, I will just do the rows one by one and cut and paste.

Thank you kindly,

JE

justearth> that always contain the value from column1 and then all possible combination

justearth> of the values from the remaining columns' values" The python script

justearth> does not do this, as is. Can this be implemented

Yes. The script solves a single row. But it can be wrapped into another loop that goes through the rows.

The modification for preserving the first value is also easy. I guess, you already have your solution. Anyway, the core of the code would be like

row = ['bbContag', 'bbED', 'bbENN_MN', 'bbLPI', 'bbLSI']

for row in get_the_row_here():

elem1 = row[0]

rowtail = row[1:]

here put the above loop

If interested I have a follow-up question:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23813830.html

I'd like to change the output format.

Thanks again,

JE

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Try the following tweak to the original macro. It puts the results on worksheet Sheet2 in adjacent columns.

Brad

Open in new window