+ Reply to Thread
Results 1 to 17 of 17

combination generator needed

  1. #1
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    combination generator needed

    Hi, I have data in the cell range A1:A52

    I'm interested in 3 item combinations.

    I'd like to generate the entire list of 22,100 three item combinations.

    How do i do this?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: combination generator needed

    of those list of 26 pairs there are only 1140 combinations,what do you mean exactly?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: combination generator needed

    See attached. Copy the formulas down 22,000-odd rows.
    Attached Files Attached Files
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Quote Originally Posted by martindwilson View Post
    of those list of 26 pairs there are only 1140 combinations,what do you mean exactly?
    There should be 52 cells, each with a unique 2 character string. If you calculate
    COMBIN(52,3) you get 22,100

    I would like a program that lists all 22,100 of these combinations. They should run something like: ackcqc, ackcjc, ackctc, etc.

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: combination generator needed

    They should run something like: ackcqc, ackcjc, ackctc
    Change the formula in E2 to

    =INDEX(Inp!$A$1:$A$52, C2+1) & INDEX(Inp!$A$1:$A$52, B2+1) & INDEX(Inp!$A$1:$A$52, A2+1)

    and delete columns F:G

  6. #6
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Quote Originally Posted by shg View Post
    See attached. Copy the formulas down 22,000-odd rows.
    Thanks for the help. When I type in the NextCombo formula, however, I get a #NAME? error...

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: combination generator needed

    It's a UDF I added to the workbook I posted.

  8. #8
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Quote Originally Posted by shg View Post
    It's a UDF I added to the workbook I posted.
    Got it. Thank you for your help.

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: combination generator needed

    my mistake my comb checker only does up to 20 items i didn't notice on copy paste!!!!!!!!

  10. #10
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Quote Originally Posted by shg View Post
    See attached. Copy the formulas down 22,000-odd rows.
    Is there a way to change this from a function to a subroutine? Rather than have an input and output sheet, I would like to have the program take the data in cells A1:A52, generate the 22,100 combinations and place them in cells B1:B22100. If possible, I would like to have this as a macro that I can run rather than manually copy a formula down 22,100 rows.
    Last edited by smiso24; 04-12-2010 at 09:59 AM.

  11. #11
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Can anyone help with this?

  12. #12
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: combination generator needed

    Try this one:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Quote Originally Posted by mdbct View Post
    Try this one:
    Beautiful. Is there a way to put the output in column B of the sheet "data" rather than the separate sheet "Sheet2"?

  14. #14
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: combination generator needed

    Sorry about that. I misread your last post.

    change this:
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Wonderful! Thank you so much!

  16. #16
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    I do apologize but I have another question. I have modified the spreadsheet such that the relevant data has been moved to column B. There is data in cell A1 which is NOT to be a part of the combination generating process. Can you re-write the code so the output is placed in column C and does not disturb cell A1 ?

    I greatly appreciate your time and effort.
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    11-07-2009
    Location
    Ohio, United States
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: combination generator needed

    Disregard my last post. I think I finally got it squared away. Thank you again for all the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1