# Thread: combinations of data (2003)

1. ## combinations of data (2003)

Hello,

I am doing a spreadsheet that has three columns with information. In the fourth column I need to put all posible combinations of the previous five columns. For example, cell a1 has the word "Test" in the cell, cell b1 has "None", cell c1 has "Null". I need to either create a formula or macro that can give me all posible combinations for the example above, it would be "Test & None", "Test & Null", "None & Null", "None & Test", "Null & Test", "Null & None". Any help would be great. Thanks.

Like this?

3. ## Re: combinations of data (2003)

Thanks stans. This works, but I am looking at analyzing more than 3 columns (total of 5 to 10 columns) and to write all the possible combinations will take too long as you have.

4. ## Re: combinations of data (2003)

Does this do what you want?

Select the range desired, in each row, starting wih the column to the immediate right of the selection, it will place the all the 2 way combinations

<pre>Option Explicit
Sub TwoWayCombos()
Dim rSel As Range
Dim rCell As Range
Dim iCols As Integer
Dim iColOut As Integer
Dim lRows As Long
Dim lRow As Long
Dim i As Integer
Dim j As Integer

Set rSel = Selection
With rSel
lRows = .Rows.Count - 1
iCols = .Columns.Count - 1
End With
For lRow = 0 To lRows
iColOut = iCols + 1
For i = 0 To iCols
For j = 0 To iCols
If i <> j Then
With rSel.Cells(1)
.Offset(lRow, iColOut) = _
.Offset(lRow, i) & _
" & " & .Offset(lRow, j)
iColOut = iColOut + 1
End With
End If
Next
Next
Next

Set rCell = Nothing
Set rSel = Nothing
End Sub</pre>

Steve

5. ## Re: combinations of data (2003)

Thank you steve that did the trick.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•