Solved

Find all possible combinations from lists in an excel spreadsheet

Posted on 2008-10-12
15
1,765 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:justearth
  • 8
  • 4
  • 3
15 Comments
 
LVL 80

Expert Comment

by:byundt
Comment Utility
One way of looking at this problem is that of counting in binary from 1 to x, where x = 2^n - 1 and n=number of words (not counting the first one). When a bit is "1", then the word is added to the string. When the bit is "0", then the word is not added to the string. For five words in A1:E1, the counting formula to build the string would be:
=$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
0
 

Author Comment

by:justearth
Comment Utility
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+bbED
bbContag+bbLSI
bbContag+bbED+bbLSI
bbContag+bbContag+bbLSI
bbContag+bbED+bbContag+bbLSI
bbContag+bbED+bbLSI
bbContag+bbED+bbED+bbLSI
bbContag+bbContag+bbED+bbLSI
bbContag+bbED+bbContag+bbED+bbLSI
bbContag


Thanks again,
Cheers,
JE
0
 
LVL 28

Assisted Solution

by:pepr
pepr earned 100 total points
Comment Utility
For Python, have a look at the standard module itertools and the generator called itertools.permutations() http://docs.python.org/library/itertools.html?highlight=permutations#itertools.permutations

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, n) and loop for 1 to the number of elements in the row. See the snippet below. See the attached result.txt with the produced results.
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)

Open in new window

result.txt
0
 
LVL 28

Expert Comment

by:pepr
Comment Utility
The above does not solve the problem of how to get the rows out of the Excel document. The easy approach (if acceptable) could be to export the rows into csv format and then read the csv file using Python's standard module csv.

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.
0
 

Author Comment

by:justearth
Comment Utility
pepr:
Exporting to csv would be acceptable, so would a text file.

Thanks,
JE
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
JE,
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

Open in new window

0
 

Author Comment

by:justearth
Comment Utility
Pepr:
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:justearth
Comment Utility
byundt:

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
0
 
LVL 80

Accepted Solution

by:
byundt earned 400 total points
Comment Utility
JE,
Try the following tweak to the original macro. It puts the results on worksheet Sheet2 in adjacent columns.
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, iCol 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 rw As Range, rg As Range

Dim s As String

Set rg = Selection

For Each rw In Selection.Rows

    N = Application.CountA(rw)

    ReDim vInputs(1 To N)

    j = 0

    For i = 1 To N

        If rw.Cells(i) <> "" Then

            j = j + 1

            vInputs(j) = rw.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

    iCol = iCol + 1 'Put the results on Sheet2 in adjacent columns

    Worksheets("Sheet2").Cells(1, iCol).Resize(NN, 1).Value = vResults

Next

End Sub

Open in new window

0
 

Author Comment

by:justearth
Comment Utility

byundt:
Awesome.

Thanks,
JE
0
 

Author Closing Comment

by:justearth
Comment Utility
Thanks.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
JE,
Thanks for the grade!
Brad
0
 
LVL 28

Expert Comment

by:pepr
Comment Utility
justearth> Pepr: Also, from above I asked "I need to create a list from these numbers
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

0
 

Author Comment

by:justearth
Comment Utility
pepr:
Thank you kindly for the follow-up. I really appreciate it.

Cheers,
JE
0
 

Author Comment

by:justearth
Comment Utility
Hello,
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
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Path to Python 9 35
MS Excel  "--" in Formula What is it doing? 3 9
TT Status Chang 3 27
onOpen 14 35
The purpose of this article is to demonstrate how we can use conditional statements using Python.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now