make sampling in excel????

[sonumm]

sir can you help me for generates samples sets in excel plz plz help me my set is consists six integers and it has 720 combination to write sample set i cant understand how can i generate sample like my set={1,2,3,4,5,6} and i have to make 720 combination like {6,5,4,3,2,1} plz help me to genrates these sample set on excel 2007 i will be very thankful to you.i m waiting for your reply........as sooooooon as possible plz i have no enough time for my assignment

take care

 

[Ruddles]

Where is your set of six integers? In a worksheet? If so, where exactly in the worksheet?

And where do you want the output written? To the same worksheet? To a different worksheet? If so, do you want each combination written to a single cell or across six cells? Or do you need them to be in a text or CSV file?

 

[Ruddles]

Try this: place your six integers in A1:F1 and run the subroutine GenerateSets:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Dim a, b, c, d, e, f[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Sub GenerateSets()[/FONT]

[FONT=Fixedsys]  Dim ws As Worksheet
  Dim irow
  
  Set ws = ThisWorkbook.Sheets(1)
  ws.UsedRange.Offset(1, 0).ClearContents
  
  irow = 1
  For a = 1 To 6
    For b = 1 To 6
      For c = 1 To 6
        For d = 1 To 6
          For e = 1 To 6
            For f = 1 To 6
              If NoDuplicates Then
                irow = irow + 1
                ws.Cells(irow, 1) = ws.Cells(1, a)
                ws.Cells(irow, 2) = ws.Cells(1, b)
                ws.Cells(irow, 3) = ws.Cells(1, c)
                ws.Cells(irow, 4) = ws.Cells(1, d)
                ws.Cells(irow, 5) = ws.Cells(1, e)
                ws.Cells(irow, 6) = ws.Cells(1, f)
              End If
            Next f
          Next e
        Next d
      Next c
    Next b
  Next a
            [/FONT]
[FONT=Fixedsys]End Sub[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Private Function NoDuplicates() As Boolean[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  NoDuplicates = True
  
  If a = b Or a = c Or a = d Or a = e Or a = f Then NoDuplicates = False: Exit Function
  If b = c Or b = d Or b = e Or b = f Then NoDuplicates = False: Exit Function
  If c = d Or c = e Or c = f Then NoDuplicates = False: Exit Function
  If d = e Or d = f Then NoDuplicates = False: Exit Function
  If e = f Then NoDuplicates = False: Exit Function
  If a = 1 And b = 2 And c = 3 And d = 4 And e = 5 And f = 6 Then NoDuplicates = False: Exit Function
    
End Function
[/FONT]

It's very clunky but it seems to do the job.

 

[sonumm]

thank you ruddles thank you

thank you sir ruddle i have no words to thank you
truly thanks from bottom of heart
one more fevour i want is it possible make these sample made on worksheet and not the use of VBA code i mean without help of VBa
by some combination of formula is it possible to make such a combinations plz plz plz reply me sooon sir you r realy great teacher God bless you
best regards

 

[sonumm]

God Bless you sir well done i have no words to say thanx this is excellent

 

[Ruddles]

Re: thank you ruddles thank you

.one more fevour i want is it possible make these sample made on worksheet and not the use of VBA code i mean without help of VBa
by some combination of formula is it possible to make such a combinations plz

I would have to say that was not possible - at least, I have no idea how to even start to do that. Sorry!

 

[sonumm]

ok once again thanx

ok Sir ruddle once again thanx you solved my problem i m very thankful 2 u for this kindness ok take care