Find Combinations, Delimit, and Paste into new Tab

apo · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

Hi..

I actually get the green triangles on all the values too...

I don't know why.. but maybe a workaround is this..

Code

For Each xCell In Sheet2.Range("A1").CurrentRegion.Offset(1)
xCell.Value = CDec(xCell.Value)
Next xCell

You could do this or create some code that paste special (multiply by 1) all the cells in the range...

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

cool apo
thinking about it is in the options error checking .. i have probably turn mine off .. can you click the green error triangle and it should show more info .. and where the error options are

snb · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

Did you consideer:

Code

Sub M_snb()
   With Sheets("Months")
        y = .Cells(1, 2)
        sn = .Rows(2).SpecialCells(2)
        ReDim sp(UBound(sn, 2) ^ y, y - 1)
        
        For j = 0 To UBound(sp)
          For jj = 0 To UBound(sp, 2)
             sp(j, jj) = sn(1, (j Mod (UBound(sn, 2) ^ (y - jj))) \ (UBound(sn, 2) ^ (y - jj - 1)) + 1)
          Next
        Next
        
        .Cells(5, 10).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
   End With
End Sub

Display More

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

Hi snb
I tested it with five numbers and five columns and it throws a few duplicates combinations?

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

the first and last row are the same all -2

snb · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

pike

That's the only one (not 'several' ;))

But no doubt you already found out why.
Amended code:

Code

Sub M_snb()
    With Sheets("Months") 
        y = .Cells(1, 2) 
        sn = .Rows(2).SpecialCells(2) 
        Redim sp(UBound(sn, 2) ^ y-1, y - 1) 
         
        For j = 0 To UBound(sp) 
            For jj = 0 To UBound(sp, 2) 
                sp(j, jj) = sn(1, (j Mod (UBound(sn, 2) ^ (y - jj))) \ (UBound(sn, 2) ^ (y - jj - 1)) + 1) 
            Next 
        Next 
         
        .Cells(5, 10).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp 
    End With 
End Sub

Display More

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

quick in a decenting order .. thats a good consideer

Code

Option Explicit
Sub M_test()
Dim y As Long, sn, sp, j As Long, jj As Long
    With Sheets("Months")
        y = .Cells(1, 2)
        sn = .Rows(2).SpecialCells(2)
        ReDim sp(UBound(sn, 2) ^ y - 1, y - 1)
         
        For j = 0 To UBound(sp)
            For jj = 0 To UBound(sp, 2)
                sp(j, jj) = sn(1, (j Mod (UBound(sn, 2) ^ (y - jj))) \ (UBound(sn, 2) ^ (y - jj - 1)) + 1)
            Next
        Next
         
        .Cells(5, 10).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
    End With
End Sub

Display More

snb · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

To be specific: it's in the left to right order A2, B2, C2, D2, E2.
Whether descending or ascending is totally dependent of the values in A2:E2

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

cool example of combinations. if you could nail the permitations it would be a good example for "hey thats cool" forum
well done

snb · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

pike

what do you mean by: 'if you could nail the permutations' ?

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

a value can not be repeated in a variation
if there is only one -2 vaule in the set you can only use it once in any combination of five .. eg you cant have -2-2-2-2-2

snb · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

Are you looking for:

Code

Sub M_snb()
   With Sheets("Sheet1")
        y = .Cells(1, 2)
        sn = .Rows(2).SpecialCells(2)
        ReDim sp(UBound(sn, 2) ^ y, y - 1)
        
        For j = 0 To UBound(sp)
          c00 = ""
          For jj = 0 To UBound(sp, 2)
             c01 = sn(1, (j Mod (UBound(sn, 2) ^ (y - jj))) \ (UBound(sn, 2) ^ (y - jj - 1)) + 1)
             If InStr(c00 & "|", "|" & c01  & "|") = 0 Then sp(j, jj) =  c01
             c00 = c00 & "|" & sp(j, jj)
          Next
        Next


        .Cells(5, 10).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
        For j = 2 To y
           .Cells(5, 10).CurrentRegion.Columns(j).SpecialCells(4).EntireRow.Delete
        Next
   End With
End Sub

Display More

**pike** · Mar 7th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

cool bananas

Code

Option Explicit
Sub m_test()
Dim y As Long, sn, sp, c00 As String, c01 As String, j As Long, jj As Long
    With Sheets("Months")
        y = .Cells(1, 2)
        sn = .Rows(2).SpecialCells(2)
        ReDim sp(UBound(sn, 2) ^ y, y - 1)
         
        For j = 0 To UBound(sp)
            c00 = ""
            For jj = 0 To UBound(sp, 2)
                c01 = sn(1, (j Mod (UBound(sn, 2) ^ (y - jj))) \ (UBound(sn, 2) ^ (y - jj - 1)) + 1)
                If InStr(c00 & "|", "|" & c01 & "|") = 0 Then sp(j, jj) = c01
                c00 = c00 & "|" & sp(j, jj)
            Next
        Next
         
        .Cells(5, 10).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
        For j = 2 To y
            .Cells(5, 10).CurrentRegion.Columns(j).SpecialCells(4).EntireRow.Delete
        Next
    End With
End Sub

Display More

snb · Mar 8th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

pike

I'm not familiar with that vernacular..... (?)

burtonwang · Mar 17th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

Thank you so very much for all the help, and I'm grateful I could highlight a potential issue that you all seemed to learn something from and solve.

pike, could you explain what the code in post #53 is for? Is this code for permutations only? I will test everything right now on my end to make sure I don't have any more errors.

burtonwang · Mar 17th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

pike, now if I'm not mistaken, the code you have in post #47 is the newest form (shorter too) that will accomplish the same thing as the previous macro (generate the combination results pasted as numbers, but starting in row J in the months tab)?

Just to clarify as this code is infinitely shorter, so I wanted to make sure that post #47 was the entire code and not a section I'm supposed to add to the existing code we were working on.

snb · Mar 17th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

It seems you didn't notice it isn't @Pike's code but mine.

burtonwang · Mar 17th 2014

Re: Find Combinations, Delimit, and Paste into new Tab

snb, you're right! The post before was that code too, terribly sorry!

Find Combinations, Delimit, and Paste into new Tab

Participate now!

Share

Users Viewing This Thread