asked on

sorting data into groups of three

I have rows of data, from which I need to sort into every possible combination of three (as per worksheet)

I think there are 18 or 20 possible combinations altogether, I have done half a dozen for illustration.

Could someone help me with code that will sort thousands of rows into this format please ?

thanks

006-triplet-studies-EE-sample.xls

GrahamSkan

You want to choose any combination of three items from six. That means that you will get 120 combinations (6*5*4). With that in mind, do you still want to proceed?

Jacques Geday

Sorry it is not clear the numbers goes from what to what ? I know they are grouped in 3 but using what digits ? 0 to 9 ?
gowflow

Bill Prew

Try this VBA procedure, should do what you want.

Public Sub Triples()
    intNumbers = 6
    Set wsData = ThisWorkbook.Worksheets("Set 1")
    For rowCurrent = 1 To wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
        colOutput = 9
        For i1 = 1 To intNumbers - 2
            For i2 = i1 + 1 To intNumbers - 1
                For i3 = i2 + 1 To intNumbers
                    wsData.Cells(rowCurrent, colOutput).Value = wsData.Cells(rowCurrent, i1).Value
                    wsData.Cells(rowCurrent, colOutput + 1).Value = wsData.Cells(rowCurrent, i2).Value
                    wsData.Cells(rowCurrent, colOutput + 2).Value = wsData.Cells(rowCurrent, i3).Value
                    wsData.Cells(rowCurrent, colOutput + 3).Value = ""
                    colOutput = colOutput + 4
                Next
            Next
        Next
    Next
End Sub

Open in new window

»bp

aikimark

18 or 20 possible combinations

In your case, 20 is the correct number for the combination. You can use the Excel Combin() function to confirm the calculation.

sorting data

I don't think that "sorting" is the correct term to use. Maybe "generating combination sets" would be more accurate a description of your problem.

Europa MacDonald

ASKER

thanks for the coments everyone

GrahamSkan the number is defo 20, thanks.
Goflow, I have detailed in worksheet. I have a group of 6 numbers, I need to automatically sort them into all possible combinations of 3, of which there are 20.
Bill Prew - How do I use that code ? (I know very little about excel)

I have added another example of what I want, hopefully it is clearer.

006-triplet-studies-EE-sample2.xls

Thanks again guys

Jacques Geday

Can I use 1 col for each set of 3 ?
Can I put in A the total number of digits you want to get the combinations out of ?

As this is neater and cleaner.
gowflow

GrahamSkan

Yes, I was calculating for permutations, not combinations. I'm glad you were able to discount my comment.

SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

Okay, based on you latest example of what you want, I have added my macro to that and attached here. Just run the Triples macro once you have data in column A starting now at A4 and below.

EE29036719.xlsm

»bp

Europa MacDonald

ASKER

Thanks for the response so far

Goflow, I need the numbers arranged as per the worksheet as I have to do further stats on them later.

Goflow and BillPrew, could you save those worksheets as 2003 xls please ? I dont have the latest copy of office.

Thanks

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Europa MacDonald

ASKER

Bill Prew thats exactly what I was looking for thanks.

Thank you to Goflow and everyone who contributed.

Bill Prew

Welcome.

»bp