+ Reply to Thread
Results 1 to 6 of 6

VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

  1. #1
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Question VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    I have been looking at this for hours trying to figure out how to do this with a pivot table or formula. I haven't found anything on the web that answers this question. I asked earlier in the Formula forum with no luck. So I'm resorting to VBA, which I don't understand or know how to write. I need two tables of combinations to use as reference for a VLOOKUP function. One table is five columns and has discrete rules, the other is four columns and has broader rules.

    I've attached a sample with the rules. Essentially what I need is something that can generate something like this (except with the specific text in each column):

    Column 1 Column 2 Column 3 Column 4 Column 5
    A A A A A
    A A A A B
    A A A B A
    A A B B A

    Anyone know a simple solution to this?

    Thank you!!!
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    We discourage duplicates but I understand your situation and won't kill the thread. However, when you do this you should provide a link to the other thread because there is a solution there you seem to have found useful.

    https://www.excelforum.com/excel-for...s-in-each.html
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    I'm sorry. I'm new and I didn't know. I thought maybe I posted in the wrong forum. John was great in providing a solution to calculating the fidelity. I still don't have a solution for generating the table of permutations. Thanks for your help,
    L

  4. #4
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    Please use in new sheet as the VBA start at A1.

    it create combination for 1, 2 and 3 for 4/5 columns, and replace will words accrodingly.

    5 combination
    Please Login or Register  to view this content.
    4 combination
    Please Login or Register  to view this content.
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  5. #5
    Registered User
    Join Date
    03-15-2018
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    27

    Re: VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    That worked flawlessly. Thank you so much!!!!

  6. #6
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: VBA to Generate Permutation of 5 columns with 3 possibilities in each column?

    Just in case if the other combination.
    this code will from 1 up to 9 or further, depend on the limitation of the usage of laptop.
    Change the read color respectively

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Generate a Permutation
    By kjb0121 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-07-2015, 01:16 PM
  2. Permutation Functions for calculating ALL possibilities
    By Apcal88 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-17-2015, 12:18 PM
  3. [SOLVED] Formula to generate third column value on the basis 2 different columns value
    By Manish_Gupta in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-20-2014, 01:52 AM
  4. Replies: 3
    Last Post: 11-12-2013, 01:36 PM
  5. Replies: 1
    Last Post: 09-18-2012, 08:50 AM
  6. Need Help: Excel 2007 - to generate permutation
    By thivan83 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2011, 06:33 AM
  7. copy each columns in a generate txt for each column
    By ArielNek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-20-2007, 10:42 AM

Tags for this Thread

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