seeking John Coleman
 

Hi John,

You helped me solve a problem recently with combining values in Excel.
I was wondering if you would mind helping me out again. I have tried to
do it myself but I just can't get it to work the way I would l like.

What I'm hoping to accomplish is similar to before but it would be
combining two of the first dialog boxes with 3 of the second dialog box
in all permutations. So, as before 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 C F H J
A E D H J
A G D F J
A I D F H

C E B H J
C G B F J
C I B F H

E G B D J
E I B D H

G I B D F

If you wouldn't mind helping out a VBA challenged chump again, it would
be greatly appreciated.

Regards,
jugrnt

seeking John Coleman
 

Hi Jugrnt,

When I saw the message title I was afraid that the FBI had finally
caught up with me.

Try this:

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

Sub CombineValues2()
Dim LHS As Variant
Dim RHS As Variant
Dim Vals As Variant
Dim n As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim currentRow As Long

Vals = InputBox("Enter list of opponents, separated by commas")
Vals = Replace(Vals, "vs", ",", , , vbTextCompare)
Vals = Replace(Vals, " ", "")
Vals = Split(Vals, ",")
n = UBound(Vals)
If n Mod 2 = 0 Or n < 9 Then
MsgBox "Invalid Input"
Exit Sub
End If
ReDim LHS(0 To (n - 1) / 2)
ReDim RHS(0 To (n - 1) / 2)
For i = 0 To n
If i Mod 2 = 0 Then
LHS(j) = Vals(i)
j = j + 1
Else
RHS(k) = Vals(i)
k = k + 1
End If
Next i
n = UBound(LHS)
Range("A:A").ClearContents
For i = 0 To n - 1
For j = i + 1 To n
For k = 0 To n - 2
For l = k + 1 To n - 1
For m = l + 1 To n
If k < i And l < i And m < i And _
k < j And l < j And m < j Then
Range("A1").Offset(currentRow).Value = _
LHS(i) & " " & LHS(j) & " " & _
RHS(k) & " " & RHS(l) & " " & RHS(m)
currentRow = currentRow + 1
End If
Next m
Next l
Next k
Next j
currentRow = currentRow + 1
Next i
End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++

I changed how the input worked. In my original version I was a bit lazy
and used 2 input boxes to load the 2 arrays. Now I have a single input
box that expects input in the form

A vs B, C vs D, E vs F, G vs H, I vs J

which strikes me as closer to what you are looking for (although it
would be easy enough to go back to the original format). The commas are
needed as pair separators but the rest is pretty forgiving, ie, A vs B
can even be written as AVSB. By the way, my original code contained a
subtle bug - it doesn't work if you put more than 1 space between input
elements. Nothing really wrong with that - I could call it a "feature"
- but it made my use of the Trim function pointless. This version
simply removes any spaces so that A vs B, Avs B, A vsB, etc. all
have the same result. I am assuming that
1) What plays the role of A, B etc is not a string containing "vs"
and
2) What plays the role of A, B doesn't contain any spaces
This seems to be consistent with your examples but might be unfortunate
if you want to have things like "The Cavs vs The Pistons" - but that
can be changed at the cost of a bit more care in the parsing. Let me
know if this is a problem.

HTH

-John Coleman

wrote:
Hi John,

You helped me solve a problem recently with combining values in Excel.
I was wondering if you would mind helping me out again. I have tried to
do it myself but I just can't get it to work the way I would l like.

What I'm hoping to accomplish is similar to before but it would be
combining two of the first dialog boxes with 3 of the second dialog box
in all permutations. So, as before 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 C F H J
A E D H J
A G D F J
A I D F H

C E B H J
C G B F J
C I B F H

E G B D J
E I B D H

G I B D F

If you wouldn't mind helping out a VBA challenged chump again, it would
be greatly appreciated.

Regards,
jugrnt

seeking John Coleman
 

Hi John,

Sorry if the message title caused you to gulp!

Once again you've knocked it out of the park! It's a thing of beauty
and was exactly what I was looking for. And, your hunch about what this
may be used for, Cavs vs Pistons, is pretty bang on except that I'm
interested in NHL games. I don't foresee the "vs" being a problem with
any NHL team names as I always shorthand the city names (mtl tor det
chi etc). Having said that, I had changed your previous dialog boxes to
"Enter Favorites, seperated by spaces" and "Enter Dogs..." which worked
well for me. If you are curious, Dogs beat the spread at about a 65%
clip in the NHL (has been pretty consistent to that number the past 3
or 4 seasons) so I'm gonna run this a little while and see how it
works.

Again, thank you very much for your help and all the best to you!

Regards,
jugrnt


John Coleman wrote:
Hi Jugrnt,

When I saw the message title I was afraid that the FBI had finally
caught up with me.

Try this:

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

Sub CombineValues2()
Dim LHS As Variant
Dim RHS As Variant
Dim Vals As Variant
Dim n As Long
Dim i As Long, j As Long, k As Long, l As Long, m As Long
Dim currentRow As Long

Vals = InputBox("Enter list of opponents, separated by commas")
Vals = Replace(Vals, "vs", ",", , , vbTextCompare)
Vals = Replace(Vals, " ", "")
Vals = Split(Vals, ",")
n = UBound(Vals)
If n Mod 2 = 0 Or n < 9 Then
MsgBox "Invalid Input"
Exit Sub
End If
ReDim LHS(0 To (n - 1) / 2)
ReDim RHS(0 To (n - 1) / 2)
For i = 0 To n
If i Mod 2 = 0 Then
LHS(j) = Vals(i)
j = j + 1
Else
RHS(k) = Vals(i)
k = k + 1
End If
Next i
n = UBound(LHS)
Range("A:A").ClearContents
For i = 0 To n - 1
For j = i + 1 To n
For k = 0 To n - 2
For l = k + 1 To n - 1
For m = l + 1 To n
If k < i And l < i And m < i And _
k < j And l < j And m < j Then
Range("A1").Offset(currentRow).Value = _
LHS(i) & " " & LHS(j) & " " & _
RHS(k) & " " & RHS(l) & " " & RHS(m)
currentRow = currentRow + 1
End If
Next m
Next l
Next k
Next j
currentRow = currentRow + 1
Next i
End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++

I changed how the input worked. In my original version I was a bit lazy
and used 2 input boxes to load the 2 arrays. Now I have a single input
box that expects input in the form

A vs B, C vs D, E vs F, G vs H, I vs J

which strikes me as closer to what you are looking for (although it
would be easy enough to go back to the original format). The commas are
needed as pair separators but the rest is pretty forgiving, ie, A vs B
can even be written as AVSB. By the way, my original code contained a
subtle bug - it doesn't work if you put more than 1 space between input
elements. Nothing really wrong with that - I could call it a "feature"
- but it made my use of the Trim function pointless. This version
simply removes any spaces so that A vs B, Avs B, A vsB, etc. all
have the same result. I am assuming that
1) What plays the role of A, B etc is not a string containing "vs"
and
2) What plays the role of A, B doesn't contain any spaces
This seems to be consistent with your examples but might be unfortunate
if you want to have things like "The Cavs vs The Pistons" - but that
can be changed at the cost of a bit more care in the parsing. Let me
know if this is a problem.

HTH

-John Coleman

wrote:
Hi John,

You helped me solve a problem recently with combining values in Excel.
I was wondering if you would mind helping me out again. I have tried to
do it myself but I just can't get it to work the way I would l like.

What I'm hoping to accomplish is similar to before but it would be
combining two of the first dialog boxes with 3 of the second dialog box
in all permutations. So, as before 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 C F H J
A E D H J
A G D F J
A I D F H

C E B H J
C G B F J
C I B F H

E G B D J
E I B D H

G I B D F

If you wouldn't mind helping out a VBA challenged chump again, it would
be greatly appreciated.

Regards,
jugrnt

seeking John Coleman
 

wrote:
(snipped for berivity)

jugrnt,

I'm glad that you liked it. A few final remarks

1) Input boxes can easily become tedious. It would be easy to modify
the sub so that it takes a list of favorites in column A and a list of
dogs in column B and then puts the output in column C
2) Upon further thought, my algorithm is not very efficient. Most of
the candidate rows that I generate are promptly discarded by the line
"If Disjoint(LTuple,RTuple)..." My hunch is that while my current
algorithm would just take seconds for up to a dozen teams or so it
might take minutes by the time you get to say 20 teams or so. I haven't
experimented with it, so I don't know exactly where the threshhold of
inefficiniency is, but it is there somewhere. If it is a problem, let
me know - I have already mentally outlined a much more efficient
approach, but it would involve more or less starting from scratch.
3) If you want to post further on this thread - just reply to this
rather than starting a new thread. Given the sheer volume of this
newsgroup and given that this is more of a hobby of mine than something
I do professionally, I could easily miss a new thread, even if my name
is in the topic. I don't want to make the FBI's job too easy.

I hope your experiments prove productive.

-John