Creating all possible combinations/permutations out of each row

[xminte]

Hi all!

I could really use your help with my Excel file. It consists of rows of purchases, each row containing all the product numbers of 1 shopping basket. It looks something as following:

Pr.1 Pr.2 Pr.3 Pr.4 Pr.5 Pr.6 Pr.7 Pr.8 ---> etc
100 101 102 103 104 105 106 107
104 103 106 107 105
105 101 100 106 103
101 100 103
107 106
105 106

You'll get the idea

But now I want, for each row, to get all combinations consisting of two numbers. Example for the 4th row:
101-100
101-103
100-103.

I have no idea how to create a formula that does this for long rows (my longest consists of 20 purchases, and the file is 1800 shopping baskets long.)

Do you guys have any suggestions on how to go and solve this problem?

Thank you in advance!

 

[lrobbo314]

There are probably better ways to do this in VBA. I am trying to learn how to use recursive programming, and even though it seems to work, I'm sure some cleaning of the code could be done.

Either way, this seems to do what you are looking for. If you don't know how do use VBA.. Go to your worksheet, hit Alt+F11, then hit Alt+I+M to insert a module, then paste the code below into the module.

Option Base 1
Public Top As Integer
Public res As String
Public AR()
Sub Main()
Dim LR As Long
Dim r As Range
Dim cel As Range
Dim tmp As Range
LR = Range("B" & Rows.Count).End(xlUp).Row()
Set r = Range("B1:B" & LR)
For Each cel In r
    Set tmp = Range(cel, cel.End(xlToRight))
    cel.Offset(, -1).Value = Combos(tmp)
    res = vbNullString
Next cel
End Sub
Function Combos(r As Range) As String
Dim IDX As Integer
Dim Cur As Integer
Set r = r.SpecialCells(xlCellTypeConstants)
AR = r.Value
Top = UBound(AR(), 2)
IDX = 1
Cur = 1
recurse IDX, Cur
Combos = Left(res, Len(res) - 1)
End Function
Function recurse(IDX As Integer, Cur As Integer) As String
Dim s As String
For i = 1 To Top
    If IDX = Top Then
        Exit Function
    Else
        Cur = Cur + 1
        If IDX <> Cur Then res = res & AR(1, IDX) & "-" & AR(1, Cur) & "|"
        If Cur = Top Then
            IDX = IDX + 1
            Cur = IDX
        End If
        recurse IDX, Cur
    End If
Next i
    
End Function

Then go back to your worksheet, Hit Alt+F8, Select 'Main', and click run.

 

[lrobbo314]

Also, I forgot to say. I have a blank column in column A, this is where the results will go. The test data you posted I have starting in column B. It has to be set up that way for this to work.