+ Reply to Thread
Results 1 to 3 of 3

Macros for Permutating values in three columns

Hybrid View

sadaslm Macros for Permutating values... 01-07-2013, 02:58 AM
Andrew-R Re: Macros for Permutating... 01-07-2013, 03:31 AM
sadaslm Re: Macros for Permutating... 01-07-2013, 04:20 AM
  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    IRmook nurse
    MS-Off Ver
    Excel 2003
    Posts
    2

    Macros for Permutating values in three columns

    I need to figure out how I can get excel to generate the list of permutations/combinations for numbers in three columns. The attached worksheet has values in three different columns (A,B,C); 10 rows for each column. I want to generate the list of permutation/combinations for all the numbers in three columns for e.g., (0.1,0.1,50), (0.2,0.1,50), (0.3,0.1,50).. and so on. Anyone know how to do this?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Macros for Permutating values in three columns

    This should do it - it assumes no header rows and will write the combinations to sheet2 (which will be cleared first)...

    Sub Permutations()
    
    Const sSOURCE_SHEET As String = "Sheet1"
    Const sSOURCE_RANGE As String = "A:C"
    
    Const sTARGET_SHEET As String = "Sheet2"
    
    Dim lLoop As Long
    Dim lItems As Long
    Dim lCols As Long
    Dim lWriteLoop As Long
    Dim lCombo As Long
    Dim lThisVal As Long
    
    Sheets(sTARGET_SHEET).Cells.Clear
    
    With Sheets(sSOURCE_SHEET)
      lItems = .Cells(.Rows.Count, .Range(sSOURCE_RANGE).Columns(1).Column).End(xlUp).Row
      lCols = .Range(sSOURCE_RANGE).Columns.Count
      
      For lLoop = 1 To lItems ^ lCols
      
        lCombo = lLoop - 1
        
        For lWriteLoop = 1 To lCols
          lThisVal = lCombo Mod lItems
          Sheets(sTARGET_SHEET).Cells(lLoop, lCols + 1).Offset(0, -1 * lWriteLoop).Value = .Cells(lThisVal + 1, lCols + 1).Offset(0, -1 * lWriteLoop).Value
          lThisVal = Int(lThisVal / lItems)
        Next lWriteLoop
        
      Next lLoop
    End With
        
    End Sub

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    IRmook nurse
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macros for Permutating values in three columns

    Thanks for the Macro Andrews! But, I see the same 10 rows repeating again and again. Please see the attached worksheet.Can you help?
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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