Since I refered you here from sci.math and noone has been interested in
it, I wrote a VBA program which does what you want to do:
+++++++++++++++++++++++++++++++++++++++++++++++++
Sub CombineValues()
Dim LHS As Variant
Dim RHS As Variant
Dim n As Long
Dim i As Long, j As Long, k As Long, l As Long
Dim currentRow As Long
LHS = InputBox("Enter left column values, separated by spaces")
LHS = Split(LHS, " ")
RHS = InputBox("Enter right column values, separated by spaces")
RHS = Split(RHS, " ")
n = UBound(LHS)
If n <> UBound(RHS) Or n < 3 Then
MsgBox "Invalid Input"
Exit Sub
End If
For i = 0 To n
LHS(i) = Trim(LHS(i))
RHS(i) = Trim(RHS(i))
Next i
For i = 0 To n
For j = 0 To n - 2
For k = j + 1 To n - 1
For l = k + 1 To n
If j <> i And k <> i And l <> i Then
Range("A1").Offset(currentRow).Value = _
LHS(i) & " " & RHS(j) & " " & RHS(k) & " " & RHS(l)
currentRow = currentRow + 1
End If
Next l
Next k
Next j
currentRow = currentRow + 1
Next i
End Sub
++++++++++++++++++++++++++++++++++++++++++++
I don't know if you have any familarity with VBA. If not, do the
following: open up the VBA editor (alt+F11). Insert a Module (from the
Insert command on the command bar) and paste the above code (not
including the ++s of course) into the module. Then, in sheet 1, say
make sure that column A is clear and invoke this macro via Tools
->Macros. You may need to adjust your macro security settings. When You
run it with the input A C E G I in the first input box and B D F H J in
the second, you get exactly the pattern you want. I put it in Column A,
but you can use the Text to Columns function in the Data menu if you
want the input spread out over the first 4 columns.
HTH
-John Coleman
(E-Mail Removed) wrote:
> Hello All,
>
> I am not very good with Excel so I hope some of you can share your
> ecpertise. What I would like to do, is take a set of values and combine
> them where one value per line (the left side) is combined with the
> remaining values of the right side in all possible configurations. I'll
> try to show an example. Given my values as such:
>
> A vs B
> C vs D
> E vs F
> G vs H
> I vs J
>
> I would like to see an output like so:
>
> A D F H
> A D F J
> A D H J
> A F H J
>
> C B F H
> C B F J
> C B H J
> C F H J
>
> E B D H
> E B D J
> E B H J
> E D H J
>
> G B D F
> G B D J
> G B F J
> G D F J
>
> I B D F
> I B D H
> I B F H
> I D F H
>
> I don't have a clue how to do this using Excel but suspect it could be
> done? The key thing is I want to combine the left side of a given value
> with the right side of the other values. If there were more or less
> input values, say it went up to M vs N, I still only want that combined
> 4 ways like M D F L but still using all the right side values combined
> with that left value. If nothing else, is there a formula I can use to
> calculate how many outcomes I should end up with given my values? In
> the above example, I had 5 different values and ended up with 20
> outcomes. What if I had 6 or 7 values?
>
> I hope this makes sense.
>
> Thanks in advance for any help you can provide or points in the right
> direction.
>
> jugrnt
Similar Threads
