Combinations from values in a column

Hi there,

I'm trying to figure out how to generate all combinations from a list
of N values in a column, let's say column A.

In particular, I want to have all combinations of 2 values, 3 values,
4, ... up to 7 values.

To give an example: Let's assume I have a list of only 3 values
(1,2,3) for which I want to have all combinations of two values. In
this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
values does not matter, i.e. duplicates should be eliminated.

I hope I could explain it sufficiently. I would be grateful for any
help.

Thanks,
Andreas
0
agraefe (1)
4/22/2008 11:53:39 PM
excel 39879 articles. 2 followers. Follow

7 Replies
629 Views

Similar Articles

[PageSpeed] 15

Use the Combin() function,

For your example, to select 2 object from a list of three use =Combin(3,2).

And just for interest's sake, if order did matter you should use the 
Permut() function.

Hope this is what you need.

"Andreas" <agraefe@web.de> wrote in message 
news:62fef7e5-b040-4166-bf7d-ea37dc1c705c@a22g2000hsc.googlegroups.com...
> Hi there,
>
> I'm trying to figure out how to generate all combinations from a list
> of N values in a column, let's say column A.
>
> In particular, I want to have all combinations of 2 values, 3 values,
> 4, ... up to 7 values.
>
> To give an example: Let's assume I have a list of only 3 values
> (1,2,3) for which I want to have all combinations of two values. In
> this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
> values does not matter, i.e. duplicates should be eliminated.
>
> I hope I could explain it sufficiently. I would be grateful for any
> help.
>
> Thanks,
> Andreas 


0
4/23/2008 6:39:03 AM
One play to generate the combinations
is to use Myrna Larson's power subroutine ..

Take away this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls
(full details inside, ready to run)

In the sample file,

In Sheet1,
1. Enter the letter C or P in A1
(C = combinations, P = permutations), eg enter: C

2. Enter the number of items involved per combo in A2, eg enter: 2
(this fig would be your: 2 values, 3 values, 4, ... up to 7 values.)

3. Enter/List the N items in A3 down (your "N values in a column")

4. Select A1 (this cell selection is required),
then click the button ListPermutations to run the sub ListPermutations

5. The results will be written to a new sheet (just to the left),
and wrap in a zig-zag manner until all combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003
--------
Repeat the runs for each of your desired values (Step 2 above): 3,4,5 ... 7

Go easy when you "ramp up" the generation
(increasing picks on increasing N values)

As a sanity check, for example:
a "Pick 6 out of 45" run will work out to a staggering:
=COMBIN(45,6) = 8,145,060 combinations
so almost half** an entire sheet would be populated
(You sure you want to do this ??)

**A single sheet in xl97 to xl2003 houses:
=65536 rows x 256 cols = 16,777,216 cells

The sub would certainly need time to complete generation
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Andreas" <agraefe@web.de> wrote in message 
news:62fef7e5-b040-4166-bf7d-ea37dc1c705c@a22g2000hsc.googlegroups.com...
> Hi there,
>
> I'm trying to figure out how to generate all combinations from a list
> of N values in a column, let's say column A.
>
> In particular, I want to have all combinations of 2 values, 3 values,
> 4, ... up to 7 values.
>
> To give an example: Let's assume I have a list of only 3 values
> (1,2,3) for which I want to have all combinations of two values. In
> this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
> values does not matter, i.e. duplicates should be eliminated.
>
> I hope I could explain it sufficiently. I would be grateful for any
> help.
>
> Thanks,
> Andreas 


0
demechanik (4694)
4/23/2008 11:07:03 AM
> As a sanity check, for example:
> a "Pick 6 out of 45" run will work out to a staggering:
> =COMBIN(45,6) = 8,145,060 combinations
> so almost half** an entire sheet would be populated
> (You sure you want to do this ??)
>
> **A single sheet in xl97 to xl2003 houses:
> =65536 rows x 256 cols = 16,777,216 cells
>

Thanks, this is exactly what I need. However, my largest problem is 7
out of 66 = 778,789,440 combinations. Is there a possibility to do
this in Excel? Or save the data in a txt file and then read it by
another application? What I need later is the median and mean of each
of the combinations.

Thanks,
Andreas


0
4/23/2008 2:20:33 PM
Mh, as I said, I need the mean / median of the combinations. Is it
possible to integrate this already in the code? Right now, it saves
the combinations separated by comma, which makes it impossible to
calculate the median / mean.

Andreas

0
4/23/2008 2:36:51 PM
<graefe.andreas@gmail.com> wrote
> Thanks, this is exactly what I need...
Welcome. Try posting in .programming for your new queries.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 


0
demechanik (4694)
4/23/2008 9:27:59 PM
On Apr 23, 5:27 pm, "Max" <demecha...@yahoo.com> wrote:
> <graefe.andr...@gmail.com> wrote> Thanks, this is exactly what I need...
>
> Welcome. Try posting in .programming for your new queries.
> --
> Max
> Singaporehttp://savefile.com/projects/236895
> xdemechanik
> ---

Thanks, Max
0
4/23/2008 9:36:27 PM
>... However, my largest problem is 7 out of 66 = 778,789,440 combinations.
>... save the data in a txt file and then read it by another application?
> What I need later is the median and mean of each.

For SPSS, I assume you will generate all 778,789,440 subsets, and then 
"Count" how many of each "mean" you have. (I'll skip Median for now).
I'll just throw this out for consideration.  Your "Mean" problem could be 
simplified with a Generating Function:
I won't list the vba code, but here is a math program to explain one way to 
do this very quickly.
Here is the generating function for your means: (y is your subset size of 7 
later)

gf = Product[1 + x^k y, {k, 66}];

Hence, we can calculate "All" Means right away from the series...

Means = CoefficientList[Coefficient[gf, y^7], x] // Rest;

(I dropped the zero indexed term)

What I mean here by Mean is the sum of your 7 numbers in each subset prior 
to dividing by 7.

The smallest subset is {1,2,3,4,5,6,7} whose total is 28.
There is only one subset that totals 28

Means[[28]]
1

or in other words, has a mean of 4.
28/7.
4.

We can immediately tell that the largest count is a total of 234 (& 235)

Means[[234]]
6,327,599

234/7.
33.4286

In other words, we can immediately see that there are 6,327,599 subsets of 
size 7 that have a mean of 33.4286 from a set of 66.

As a quick check, if I total all the solutions...
Means // Total
778,789,440

It matches the expected sum.
Binomial[66, 7]
778,789,440

Which checks with Excel:
=COMBIN(66,7)
778,789,440

- -
HTH  :>)
Dana DeLouis


<graefe.andreas@gmail.com> wrote in message 
news:dcf2d950-4e61-437a-af6e-7c35800a3546@27g2000hsf.googlegroups.com...
>> As a sanity check, for example:
>> a "Pick 6 out of 45" run will work out to a staggering:
>> =COMBIN(45,6) = 8,145,060 combinations
>> so almost half** an entire sheet would be populated
>> (You sure you want to do this ??)
>>
>> **A single sheet in xl97 to xl2003 houses:
>> =65536 rows x 256 cols = 16,777,216 cells
>>
>
> Thanks, this is exactly what I need. However, my largest problem is 7
> out of 66 = 778,789,440 combinations. Is there a possibility to do
> this in Excel? Or save the data in a txt file and then read it by
> another application? What I need later is the median and mean of each
> of the combinations.
>
> Thanks,
> Andreas
>
> 

0
ddelouis (141)
4/25/2008 4:27:04 AM
Reply:

Similar Artilces:

Mails Combined
HI, I mistakenly delete all mails from Inbox and then found them on the deleted Items and selected all and ask to move folders back to Inbox. the problem is all mails were combined into one email and moved back in to Inbox as a single email. How do i go back to the original version. How do i uncombined all the mails in to individual mails from the right recipient? Please help ASAP.. Submitted using http://www.outlookforums.com ...

Adding numbers in columns with letters
I need a little help. How do you add up the values of numbers in column which has letters next to the numbers. I have a column o numbers, but the letters "mb" are a space bar away from the numbers. Everytime I use the function to add the numbers I get a value of zero. Could anyone please help me out? Thanks in advance -- Message posted from http://www.ExcelForum.com Assuming you mean the "numbers" all end with " mb", e.g. "123 mb", you can add a "Helper" column containing a formula like this: =--LEFT(A1,LEN(A1)-3) Copy down as far as...

merge/combine workshhets
I need to merge about 75 worksheets into one spreadsheet on a regular basis - all have exactly the same format/layout etc back can vary in the number of rows Try this Jeff http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Jeff224" <Jeff224@discussions.microsoft.com> wrote in message news:C6790506-2888-4F4D-80EA-BB0D9F9B27A2@microsoft.com... >I need to merge about 75 worksheets into one spreadsheet on a regular basis - > all have exactly the same format/layout etc back can vary in the number of > rows Or http://www.rondebru...

edit network script for window pop-up, store values... help..
ok, I have a script that compares two dates: A scheduled pickup date - column J and The Acctual Picked up date - column K in order to be compliant, the time frame between these two dates can't not take longer then 2 days from the scheduled pickup date. Right now, the script when I run it, looks at all the data and in column N prefilles the =NETWORKDAYS formula and then runs the statistics, showing anything less then 0 as red and more then 2 as red, 0, 1, 2 as black. Then on the spreadsheet I have a formula that looks at the column N and counts the number ranges and figure out the per...

Finding the combination that appears more times
Hi everybody i have a list on excel 2007 that displays the purchase of items on columns A:E, each column showing one (1) item I want to find what combination of items appears more times, especifically, which combination of four (4) is the favorite mix eg: A B C D F A C D E F B C D E F B C D F Z in this example, the winner would be (B, C, D, E) as it appears 3 times the main problem is that I have over 1,000 rows and trying with =SUMPRODUCT(COUNTIF(A1:E1,$N$1:$R$1)) por each combination would take forever... is there a simpler, quicker ...

Combining 2 Excel (xls) files
I have 2 xls workbooks with different sheet names and both with different range names. I would like to combine them both into one workbook so that I don't have to re-create all of the range names. Is there a way to do that? -- LAF ------------------------------------------------------------------------ LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9656 View this thread: http://www.excelforum.com/showthread.php?threadid=346017 Hi LAF- Open both files (let's call them A & B, and assume A will be your "combined" file). Working i...

cell value goes up by one count
,Hello Again Could i get some help with this. How does one get an activecell , that has a value of 1, that is copied from sheet1 to sheet2 so that the value changes from 1 to a 2 and so on depending on how many times I copy a sheet. I hope this makes sense Thanks in Advance Allan =sheet1!(a1). I think you are asking for this formula. It will change the value of current cell (doesn't matter on which sheet you are currently working) to value of Sheet 1's A1 cell. Means if Sheet 1 A1 = 100 and you are currently working on Sheet 2 F1, then this formula will show the F1 value ...

Combining Publications
Can I combine two different publications into one? Suzi wrote: > Can I combine two different publications into one? ========================================= Personally, I would simply open two instances of Publisher (one document in each) / tile the two screens...and copy/paste. Maybe the following link will be useful: Combining Publisher Documents http://tinyurl.com/2lpj5w -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.com/aczzp Notice This is not tech support I am a volunteer Solutions that wo...

Can I format an entire row based on the value of a single cell?
I would like to use the conditional formatting feature to format an entire row rather than just a single cell. For example: If the cell value = "Total" then the entire row is bold. select your range (multiple rows???). Then with A3 (say) the activecell in that selection: format|Conditional formatting formula is: =$a3="total" apply a nice format LTShelley wrote: > > I would like to use the conditional formatting feature to format an entire > row rather than just a single cell. For example: If the cell value = "Total" > then the entire row is bo...

How do I unwrap text to columns?
I have a spreadsheet where the addresses have been saved as wrapped text in one column. I want to spilt the addresses into columns as Address1, Address2 etc but I can't enter the wrap character (which I think is ALT+ENTER) as a delimiter in the Text to Columns wizard. I have also tried search and replace but again Excel will not recognise ALT+ENTER as a delimiter Any ideas much appreciated! Thanks Re-format the column to eliminate the wrap.............then use Data > TextToColumns with Alt010 (from the keypad) as the delimiter Vaya con Dios, Chuck, CABGx3 "ChristineR&...

Reports in column format
My report page set-up is for 3 columns with zero spacing between columns. There is a box around the set of controls. I have the palette narrowed to the absolute minimum width so that there is no working space to the right of my controls. The result is a calendar-like presentation. The problem arises when I want to center text in the headers and footers. If I simply place my header text in the header section, it will appear more or less towards the left side of the page. In order to get it centered, I have to widen the palette and then center the text manually. If I do this, then...

Q) Last row/column ------ excel formula query
Kindly suggest what is the best way to reference the following in excel formulas: +) last row of a worksheet that has some data +) last column of a worksheet that has some data Regards, Sandeep Hi Sandeep In VBA the best thing is to use this functions You can use this in your code then for example Lr = LastRow(Sheets("Sheet2")) Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:="*", _ After:=sh.Range("A1"), _ Lookat:=xlPart, _ LookIn:...

Fill values for formula
I am trying to write a formula that will do the following: if a cell i fill in with color, do not sum values, otherwise sum values. However I do not know what the fill variable is to put in the formula. Hop this make sense. Please help. Thank You -- Message posted from http://www.ExcelForum.com Hi gardener! See: Chip Pearson: http://www.cpearson.com/excel/colors.htm Watch out for colors that result from conditional formatting as they are treated differently from "manually" imposed colors. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Exc...

Bubble chart with multiple values per series
Hi all, I've got data that looks like this: Business Unit Interest Influence/Authority Role Cards & Payments 7 9 5 Internet 10 8 2 Cards & Payments10 5 2 I'm trying to create a Bubble chart (in Excel 2007) that has: 1. Interest as the X-axis 2. Influence/Authority as the Y-axis 3. Role as the bubble size 4. Business Unit as the Series name If there is only once instance of a particular business unit, then this doesn't appear to be a problem. It's a bit ma...

Creating array from matrix with blank values
I have a matrix in the following format 1 X X X X X X X X X X X 3 X X X X X 4 X X X X X X X X where 'X' is a blank cell. I need to get the data in the form 1 3 4 Any suggestions? I tried a nested if statement but it doesn't work since there are more than 7 ifs. Andryll, If there is only one value in each row, then simply using =SUM(A1:L1) where A1:L1 is your top row, then copied down for two more rows, will give you your desired result. HTH, Bernie MS Excel MVP <Andryll.Davis@gmail.com> wrote in message news:1160141392.644344.173360@i3g2000cwc.googlegroups.com......

Can't divide the columns by twelve anymore
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC On this forum someone taught me to divide columns by twelve -- ie everything above by twelve you just put: <br> =B52/12&#09; <br> =C52/12 <br> =D52/12 <br> =E52/12 <br> =F52/12 <br> etc. <br> Now when I do that I get this: <br> <a href="http://tinypic.com/r/15wnc3o/6">http://tinypic.com/r/15wnc3o/6</a> Click OK and it will open a debugging tool that will point to the problem for you. On 27/11/09 4:15 AM, in article 59bada6c.-1@...

How to Combine Data in Different Columns
Hi Everyone I need help in this issue. I have tried many ways though some wer successful but they were not efficient. I have say 5 columns of data and the data could be on the same rows o all 5 columns or on different rows hence if I need to combine all th data of these 5 columns, I would have to sort or filter each of the manually and copy and paste to a different worksheet or column. This i very tedious and time consuming. I also tried to write a macro to loo for data in one column then put them into another column but i was no successful. However, experts like you guys shouldn't have ...

combinations
I have a finite number of values that represent length (right now I have less than 20 values). Can Excel take these values and list all possible combinations of the values? Any pointers appreciated. Stephen R. Stephen, No. There are 2,432,902,008,176,640,000 possible ways to combine 20 different values. That is, unless you have another limitation, like only using 4 at a time.... HTH, Bernie MS Excel MVP "Stephen R" <nospam@nospam.com> wrote in message news:u9ZiYhOeFHA.1456@TK2MSFTNGP15.phx.gbl... > I have a finite number of values that represent length (right...

XML int column duplication & column alphabetizing
I have an XML file saved as a .xls file. It was dynamically created i VB.NET through a web app, pulling data from a SQL Server database (i that makes a difference). Here are my 2 problems: When I open the file in Excel 2002, it duplicates every column that ha only integers in it, adding a *#agg* to the duplicate column title. don't want the columns duplicated (obviously). The second issue is that it is alphebetizing my columns. I don't wan them alphebetized. I want them in the order in which they are listed. Here is my XML code. You could simply cut and paste this into a blan text ...

ClearContents if #VALUE! found in cell
Hi All........ This code works fine for a text string as "oldest", but I want it to work if the cell value is #VALUE! (basically, clear or delete the row is the #VALUE! error is found) Dim oldest As String oldest = Range("data!k1").Value 'value is a text string With Sheets("ALL12") lastrow = .Cells(.Rows.Count, "a").End(xlUp).Row For r = lastrow To 12 Step -1 If .Cells(r, "b").Value Like oldest Then .Rows(r).EntireRow.ClearContents End If Next r End With Any help would be ...

Scatter chart for real-time tracking of a single cell value ?
Hi. I use Excel 2003. On a spreadsheet connected to a DDE data feed, I have a cell containing an integer number that updates and changes constantly during the day. I would like to chart the number in that cell in real-time second by second between certain hours of the day. I have been told that it may be possible to use a scatter chart to do this but I have not been able to make it work or to find useful help files. It seems to me that the problem has to do with charting a single cell rather than a range but I'm not sure. Any practical advice or pointer to a "how-to" web site w...

Increase space between values on y coordinate
I have a chart with months along the x axis and temperature readings along the y axis. Several years are plotte din different colors. There is also a horizontal line representing the annual average for each year. The problem I'm having is that, the annual averages are very similar and therefore one area of teh chart is very busy. I'd like to increase the y value height (ie make the space between each temperature gradient larger). Currently, I have minimum at 1 and major unit at 5. I don't mind if the rest of the chart is enlarged as well. I assumed gap width woudl do thi...

help needed to find value and add values in corresponding cells
hello. i need to first search a workbook for a number and return the value i the cell 3 over from it. i can do this using vlookup. but in som workbooks there are mutliple values (the one i look for) with mutipl values accross. eg part no. quantity 1 2 1 -- ghyne ----------------------------------------------------------------------- ghynes's Profile: http://www.officehelp.in/member.php?userid=51 View this thread: http://www.officehelp.in/showthread.php?t=75150 Visit - http://www.officehelp.in | http://www.officehelp.in/archive/index.php | http://www.officehelp.in...

Adding columns in a query
Hi, this is very strange. I am trying to add 2 columns in a query but it does not come out right. Column1 Column2 Column3 $15.00 $30.00 $15.00$30.00 Column3 should equal $45.00. When I subtract, divide or multiply it's fine. It does not work when i tried to add. this is the code behind Column3: Format(nz([Column1])+nz(Column2), "Currency") Thanks in advance -- Message posted via http://www.accessmonster.com It is interpreting the + as a concatenation operator. Try this instead: Format(Val(nz([Column1]))+Val(nz(Column2)), "Currency") On Jan 18, 8:2...

Spreadsheet will not return value of formula
I have a spreadsheet that will not return the value of a formula. It only states the formula. If I have say a column of numbers A1 to A6 and put the formula =sum(Aa1:Aa6) in A7 all that appears in A7 is the typed in formula not the actual sum. If I copy the column of numbers to another sheet in the workbook and put in the same formula I get back the actual sum???? In the offending workbook, check Tools >> Options, View tab, Window options section, uncheck Formulas box. Ed <jmadden35@verizon.net> wrote in message news:1161202257.037450.149520@e3g2000cwe.googlegroups.com... >...