COMBIN and listing - Help Required Please.

I am looking for some assistance to achieve the following which I
would prefer if possible to be able to carry out by using formula
functions rather than macros which are not my forte.

I have used the COMBIN formula to achieve the figures I require,

eg: COMBIN(6,3) to give me 20

what I need is a formula that will actually list the 20 unique
combinations,

ie: 

1 2 3
1 2 4
1 2 5
1 2 6
2 3 4
2 3 5 

etc. etc.

I have many of these of varying figures to carry out and to enter them
by hand would be laborious and open to human error.

I would appreciate any help or advise.

Thank you in advance,

Terry.
0
2/11/2008 7:52:55 AM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
632 Views

Similar Articles

[PageSpeed] 21

Terry,

I don't know of a formula that will do it but this macro. Right click the 
sheet tab, view code and paste this in.

 Put your numbers in column A start ing in A1 and run the code. The 
combinations will be output to column B. 

Sub combinations()
last = Cells(Rows.Count, "A").End(xlUp).Row
    For I = 1 To last - 2
        For J = I + 1 To last - 1
            For K = J + 1 To last
                Cells(L + 1, 2) = Cells(I, 1) & Cells(J, 1) & Cells(K, 1)
                L = L + 1
            Next
        Next
    Next
 End Sub

Mike

"Terry" wrote:

> I am looking for some assistance to achieve the following which I
> would prefer if possible to be able to carry out by using formula
> functions rather than macros which are not my forte.
> 
> I have used the COMBIN formula to achieve the figures I require,
> 
> eg: COMBIN(6,3) to give me 20
> 
> what I need is a formula that will actually list the 20 unique
> combinations,
> 
> ie: 
> 
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 2 3 4
> 2 3 5 
> 
> etc. etc.
> 
> I have many of these of varying figures to carry out and to enter them
> by hand would be laborious and open to human error.
> 
> I would appreciate any help or advise.
> 
> Thank you in advance,
> 
> Terry.
> 
0
MikeH (222)
2/11/2008 12:31:01 PM
Another play is to use Myrna Larson's power subroutine
to generate the combinations ..

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), ie 
enter: C
2. Enter the number of items involved per combo in A2, ie enter: 3
3. Enter/List the 6 items in A3 down, ie list in A3:A8  :1, 2, ... 6
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 appear 
like below, in a zig-zag manner* until all combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003

1, 2, 3
1, 2, 4
1, 2, 5
1, 2, 6
....
4, 5, 6

Go easy when you *ramp* up the generation ...
eg a "Pick 6 out of 45" run works out to a staggering:
=COMBIN(45,6) = 8,145,060 combinations
so almost half** an entire sheet would be populated

**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
--- 
"Terry" <technoholic@bigfoot.com> wrote in message 
news:kivvq3l2mk9lkgqa5pla0r13kano0sfkv7@4ax.com...
>I am looking for some assistance to achieve the following which I
> would prefer if possible to be able to carry out by using formula
> functions rather than macros which are not my forte.
>
> I have used the COMBIN formula to achieve the figures I require,
>
> eg: COMBIN(6,3) to give me 20
>
> what I need is a formula that will actually list the 20 unique
> combinations,
>
> ie:
>
> 1 2 3
> 1 2 4
> 1 2 5
> 1 2 6
> 2 3 4
> 2 3 5
>
> etc. etc.
>
> I have many of these of varying figures to carry out and to enter them
> by hand would be laborious and open to human error.
>
> I would appreciate any help or advise.
>
> Thank you in advance,
>
> Terry. 


0
demechanik (4694)
2/11/2008 12:56:10 PM
Mike & Max

	Thank you both very much for your help and your fast
responses. I have decided to use the option supplied by Max as it will
be easier for me to use as my ability with Excel is quite limited.
This does not of course detract from my gratitude to you both for your
efforts.

Regards,

Terry.





0
2/11/2008 2:07:57 PM
Welcome, nice of you to post back here.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"Terry" <technoholic@bigfoot.com> wrote in message 
news:49l0r390cp3qsn3hf19fhffr8e1qj454hd@4ax.com...
> Mike & Max
>
> Thank you both very much for your help and your fast
> responses. I have decided to use the option supplied by Max as it will
> be easier for me to use as my ability with Excel is quite limited.
> This does not of course detract from my gratitude to you both for your
> efforts.
>
> Regards,
>
> Terry.
>
>
>
>
> 


0
demechanik (4694)
2/11/2008 11:25:56 PM
Reply:

Similar Artilces:

Need Help with RAS and Outlook
I have a Mixed-Mode network, with Windows NT 4.0 as my domain controller and Logon Authorizer and Windows 2000 Server as my RAS machine. I have set my IP Address Assignment with 5 static IP address pool. The dial-up works fine, I get dial tone every time and connect. Most of my users are people who use the same laptops (all with Windows 2000 Pro) in the office and remotely. All users have checked the box "Use Windows Logon" in their dialing properties. Since they use the same laptop in both cases, the login domain is always the same. This all seems to work fine for email throug...

Refund check, help?
I am trying to print refund checks to several customers. I have both receivables and payables and I do not see the option to create a refund check. Is there someplace I go to set this up? Carmie, If you have the module, it will be located under Transactions->Sales just below Apply Sales Documents (and assuming you have rights to perform this transaction) To check to see if you are registered for the module look at Help->About Dynamics GP-> Options. It is a simple process once the setups are complete. Contact your VAR and tell them you need it installed and get then to tra...

how to set up an outlook 2000 filter rule. Help please
I'm using the outlook 2000 now is because I love the calenda and other functions. But I'm very frustrated that I couldn't set up a same junk mail filter rule as my outlook express. in my outlook express, I set a filter rule to move or incoming emails to a junk mail folder except the incoming emails are from certain domains or some individal account. This filter rule works perfect for me. Unfortunately, I was unable to set up a such rule in my outlook 2000. Any possbile help? thanks a lot! henry In news:1131133021.035942.279190@g14g2000cwa.googlegroups.com, henry <henry.huil...

AOL to Outlook 2003, transfer contacts and favorites, help?
Moving from 7 years of AOL service to a new ISP and using Outlook 2003 now. How do I avoid having to copy/paste hundreds of contacts one at a time from my AOL address book to Outlook. Also have lots of 'favorites' on AOL I would like to keep... Jakfrost "Jakfrost" <Jakfrost@discussions.microsoft.com> wrote in message news:924061C2-70C6-45DE-BA7D-9533902A9783@microsoft.com... > Moving from 7 years of AOL service to a new ISP and using Outlook 2003 > now. > How do I avoid having to copy/paste hundreds of contacts one at a time > from > my AOL addres...

Multipage control help
I am using a "Multipage" control on top of my Userform and I need help understanding the difference between the following associated events: "Change()" vs "Layout" Each time someone presses one of the tabs of the "Multipage" control, it appears that both "Multipage1_Change()" and "Multipage1_Layout()" events get called. I just need help understanding each event. thank you! Highlight just the word Layout in the procedure statement. Click F1. Choose the MSForms option. And do the same with the word Change in...

Help using if condition and vlookup
Hi ... I have set of values in a column. using the if condition i need to lookup the values from one column and match with another column with adding up values for eg., Name Value A 25 B 15 A 15 B 30 C 20 D 25 C 10 please help in finding a solution using ISNA I think you mean =SUMIF(A:A,"A",B:B) -- HTH RP (remove nothere from the email address if mailing direct) <saraskandan@gmail.com> wrote in message news:1132760279.278844.197750@f14g2000cwb.googlegroups.com... > Hi ... > > I have set of values in a column. using the if condition i need to > lookup th...

Searching Through Long List of Completed Activites
Is there any way to use a seach tool to search through completed activities on the history tab? We have converted Activities in from Goldmine and I am already on page 48 and still only now in the B's and you can guess how manu records there might be. There is no Find availble on any menu. Any suggestions? Thanks! Tools > Advanced Find and select Activities from the drop-down. Does this do what you are looking for? Matt Wittemann http://icu-mscrm.blogspot.com "Shauna Koppang" wrote: > Is there any way to use a seach tool to search through completed activities &g...

combining IF and AND
I want my cell to the following: If cell E7 does not equal zero and K7 does equal zero then in J7 display "100.00%" But if E7 does equal zero the display an emply cell. But if E7 doesn't equal zero and neither does K7 then do the formula (1-(K7/E7)). End I just can't figure out how to do this...I can enter my statements into an AND function but it only display true or false. Why can't I decide what it displays? Please help if you can. Thanks in advance Travis Dahlman =IF(E7=0,"",IF(AND(E7<>0,K7=0),TEXT(1,"0.00%"),IF(AND(E7<>0,K7...

CRM and SBS 2003
I cannot log into CRM. I have SBS 2003 installed and working great. I followed the directions in Chapter 15 of the IG for the CRM. The message that I get is: User Access Error The system could not log you on. This could be because: you are not a Microsoft CRM user, or ... I have followed everything and double-checked everything (or have I). Please help... Ok, do you have to absolutly use SBS 2003? it is very finicky when it comes to installation, and it tends to complain if you changes things after you intall.. recomenda >-----Original Message----- >I cannot log into CRM...

Urgent help needed... Template Wizard with Data Tracking???
This function has disappeared after Excel 2000. Is there another way to do the same thing in 2003? Cheers Try this link: http://support.microsoft.com/default.aspx?scid=kb;en-us;873209 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Ro Cathain" <ro_cathain@hotmail.com> wrote in message news:Wzo7f.17814$R5.1730@news.indigo.ie... This function has disappeared after Excel 2000. Is there another way to do the same thing in 2003? Che...

Help me #4
I have added information on my web page www.mykaltx.com to help. I got some great input from some of the other users. I appreciate all the help. The spreadsheet is now able to calculate the weight of steel beams pipe, angle channel and plate. What I need now is for it to calculate the weight of a trapezoid. Th spreadsheet will have to use the weight for plate. The kicker is i will have to calculate the weight for plate the top width the botto width and the length as well. If you have any ideas let me know. Also I am not that great at explaining myself. Let me know if I need t clarify. ...

Tried to link/set up email accounts. Now Outlook won't open. Help!
When Outlook opens it unsuccessfully tries to link to my email accounts (a series of dialogue boxes open looking for my email exchange server on UCSF). When the program discovers it can't link, it eventually just closes Outlook. Is there a way to delete the previous info about my email accounts, so that I can start, at least, using the calendar function. Thanks! Use the Mail Applet in the control panel "Jason Earle" <Jason Earle@discussions.microsoft.com> wrote in message news:A89E438E-665A-4E4A-8BDA-31D7BBE27CEF@microsoft.com... > When Outlook opens it unsuccessf...

List Validation + Colors
Hello, I have a list of letter for a drop down validation. A (Letter is black and the cell is filled in red) B (Letter is black and the cell is filled in green) C (Letter is black and the cell is filled in yellow) When I select the letter in question, only the black letter appear. the cell remains white. What can I do??? Hi you can't use different colors in this listbox -- Regards Frank Kabel Frankfurt, Germany "Benoit" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:09fe01c4aaf3$02efc7a0$a601280a@phx.gbl... > Hello, > > I have a list of ...

Macro help #6
I can't figure out how to set up VLOOKUP formula with 2 variabl elements. After the intial formula runs in the specified cell, I want 2 element changed: the location of the result and the lookup value. In the case below that would be B1 and A2. Range("B1") = Application.WorksheetFunction.VLookup(Range("A2" Range("data"), 2, False) I've tried setting it up like this: Dim i, k As Integer For i = 1 To 2 For k = 1 To 2 Range("B" & k) = Application.WorksheetFunction.VLookup(Range("a" i), Range("data"), 2, False) Next Next ...

Find the lowest value in a list linking it to a description.
In an Excel spreadsheet I have a long list of values for which I would like to determine the smallest value and also bring along with it the description of the value. For example: D4 25 (the smallest value in the list), A4 JoeB (value description) -- Thanks JoeB =INDEX(A2:A200,MATCH(MIN(D2:D200),D2:D200,0)) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "JoeB" <JoeB@internet.com> wrote in message news:C2251802-7717-4F8E-BE96-4A8CA18C746D@microsoft.com... > In an Excel spreadsheet I have a...

drop down list selection
I have an estimating form that we use for estimating service jobs. I entered a dropdown box at the very top to allow the user to select the correct customer. However, there are special pricing rates that pull from another sheet ('Special Rates') this sheet is in order by customer account number. However, when a customer name is selected from the dropdown box in C83, the formula set up in the preceding column E84, at needs to pull the correct customer account number to allow for the rest of the sheet to calculate properly is, =IF(E84=FALSE,LOOKUP(C83,CustomerName,'SPEC...

combine view
How can i create a combine view of two entities(Account, Contact) ? ...

Formula Help 04-26-10
In my "Total Days" column, I have the following formula and it works: E4 is Date sent to Vendor =DATEDIF(A3,TODAY(),"d") now, I want to add to this formula, if possible, to say if a date is entered into the second column, then just calculate "Date Sent" - "Date Returned" in the same "Total Days" column...is this possible?? I manually entered the "1" Date sent to Vendor Date Returned From Vendor Total Days out to Vendor 4/22/2010 4 4/22/2010 ...

i need help with error on deleting rows in vba
currently I am using this code; Set SourceWB = Workbooks("book1.xls") With SourceWB.Sheets("sheet1") Set rnrange = .Range("g11:g30") Set rnrange = rnrange.SpecialCells(xlBlanks) rnrange.EntireRow.Delete End With the error I am getting is run time error '1004': no cells were found g11:g20 are filled, it should be deleting rows 21 thru 30? -- GregJG ------------------------------------------------------------------------ GregJG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=11063 View this thread: http://www.excelforum....

scrollbar cant display,hope someone can help me
hi, all i create a project using vc++(vs2005), have a dialog and some controls incluing combox and scrollbar etc when the project run in windows xp, the scrollbar can display normally. when it run in windows vista of opening transparent effect, the scrollbar cant display in combox, but can drag and click. who can tell me why and how to solve it. thanks a lot ...

Urgent Help :QTY in Store has changed to Zero
I am having a very wierd problem with the newly installed RMS V2. The thing is i updated the stock of the store after taking the physical inventory count. The stock was updated perfectly that day and i know it because i checked it after updating the stock. But today when i checked the stock, the stock count was changed to zero in all items , and the funny thing is the item history its showing the stock correctly. The item history and the the stock on hand does not match. Can any one tell me if they had problem like this and how to figure this out. HELP!! -- Ahoo ...

List numbers and auto-formating of remaining document
I was happy to find that Word will add a numbered list after I start it but not so happy that when I change back to non-list typing my margins, tabs, etc. change. Can't seem to find where to turn off this automation. It appears that at one point I did somehow turn it off but also lost my automated list function. When I eventually (somehow) got my ability to automatically create a numbered list back that margin changes, etc. returned also. Where can I turn off the change in margin settings and still automatically created list numbers? ...

Public Folder Help Needed
I created a public folder which I gave limited rights to some of my users. I was the owner of the folder. Now, when I click on the properties, it tells me that I'm a publishing editor, and I can't make any changes to the form used, give additional users permission, etc. I tried logging in as the administrator, and it says that is a publishing editor as well. How can I give myself owner rights? Thanks, Glenn You'll need to edit the permissions using ADSI Edit. See the article below. ADSI Edit will get you out of alot of jams if you remove yourself from the exchange hiera...

Creating a new list with Last Order Date
I do have a customer transaction history. I would like to get the latest order date with amount in to another sheet instead of all the orders under same account number and bring the rest of the corresponding info to the other columns. Such as bill to information for my new sales rep. Can someone help me? Thanks Vedat A2=Customer Number B2=Order numbers C2=Order dates D2=Order Amounts D3=Bill to Name etc. A2 B2 C2 D2 CA123 1234 10/11/06 $500 CA123 2345 09/10/06 $200 CA123...

a required installation file SKU111.CAB could not be found
Hi. I have an XP with Office 2003. I've had Publisher 2003 for years and a couple of weeks ago I installed Publisher 2007. I've been using it and it's worked fine until this morning. Now when I try to open an existing Publisher document (which I just used yesterday), I get an error message stating: "Installation Error: File not Found" A required installation file SKU111.CAB could not be found Original Installation Source Required: It's telling me to install my original Office 2003 CD (which I can't find). Is there something I can do since I don...