Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: combinations of data (2003)

    Like this?
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •