combining values ?

[chipl3ader]

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

 

[Guest]

Hi,

I think your problem is a bit complicated and has a lot to do with
combinatorics. For example, if you were given 3 letters A, B and C, how much
combinations could you do? The answer is 3x2x1 = 3! = 6. Mainly,
ABC
ACB
BAC
BCA
CAB
CBA

You must think of a bag containing all the characters you need. Then design
a macro that counts how many times have you used that letter. In the example
at hand, two (2 = 3!/3) of the six strings must start with the same letter
(you can only use A twice at the begining of each string). Once you have
started, follow the same logic but with (3-1)! = 2 items. You will also find
useful the concept of recursiveness. For example, the following function
calculates n!, where n is a positive integer.

Public Function f(n As Long)
If n = 0 Then
Call MsgBox("0! is not defined!") 'I'm not sure of this
ElseIf n = 1 Then
f = 1
Else
f = n * f(n - 1)
End If
End Function

 

[chipl3ader]

Thanks very much for help Carlos!! It is greatly appreciated. I will
give it a try.

Cheers,
jugrnt

 

[John Coleman]

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

 

[chipl3ader]

wow John !!! I'm blown away! That is exactly what I was looking for. I
cannot possibly thank you enough. It works like a charm. Thank you so
very much.

I wish upon you good fortune in all aspects of life.

Regards,
jugrnt