I'd like to create a process that would allow the user to pair up every
possible combination of two items in an array and make a comparison of
the two (let's just say they pick the one they like better. I assume I
could use nested For-Next loops to do the work something but I'd like
input on three things:
1) Avoid repeating combinations, for instance: Compare A to B and later
B to A
2) Avoid comparisons to self: Compare A to A
3) Randomize comparisons. If using For-Next, it would first compare A
to all other options, then B to all other, then C, etc. How could I
skip around but be sure that All possible combinations are asked but
not repeated?

Thanks for the advice.

- John

I would generate an array of combinations, then randomly select from that
array, removing each pair that I process.

For generating pairs, you could modify this code from John Warren to return
an array of pairs of indexes into your array.

'
' Posted by John Warren, Excel-L
' March 27, 2001

Sub Combinations()
Dim n As Integer, m As Integer
numcomb = 0
n = InputBox("Number of items?", "Combinations")
m = InputBox("Taken how many at a time?", "Combinations")
Comb2 n, m, 1, "'"
End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Sub Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String)
If m n - k + 1 Then Exit Sub
If m = 0 Then
ActiveCell = s
ActiveCell.Offset(1, 0).Select
Exit Sub
End If
Comb2 n, m - 1, k + 1, s & k & " "
Comb2 n, m, k + 1, s
End Sub

--
Regards,
Tom Ogilvy



"John Michl" wrote in message
oups.com...
I'd like to create a process that would allow the user to pair up every
possible combination of two items in an array and make a comparison of
the two (let's just say they pick the one they like better. I assume I
could use nested For-Next loops to do the work something but I'd like
input on three things:
1) Avoid repeating combinations, for instance: Compare A to B and later
B to A
2) Avoid comparisons to self: Compare A to A
3) Randomize comparisons. If using For-Next, it would first compare A
to all other options, then B to all other, then C, etc. How could I
skip around but be sure that All possible combinations are asked but
not repeated?

Thanks for the advice.

- John

Depends on the usage.
But no smple answer:
Following is Air code to get the general idea
the final will be long but not that complex depending on your VBA ability

(assuming only 26X26 array----A-Z X A-Z)

first step a column of text (2 char) created by a macro that creates all
options

strtext1 = 'first char
strText2 = 'second char

AA
AB...
BA
BB....

if text1= text2 then row.delete (reminder start from the bottom and work
upwards when deleting rows)

checkme = strtext1 & strtext2
checkme =strtext2 & strtext1

Next To filter your list you need unique items, looking forward and
backwards you cannot use a simple method sooo....For unique selections look
at J_Walk code - alter code to check if the "backwards " version is also
unique

http://www.j-walk.com/ss/excel/tips/tip15.htm

For randomizer:
take the resulting list of all options you want to use and place them in a
column,
in the adjacent column place a random number = RAND()

sortteh block of 2 columns on the random number column asc and your column
of options is randomized. (it can be re-randomized on calculation and resort)

now deal with the unique, random list as you see fit.




"John Michl" wrote:

I'd like to create a process that would allow the user to pair up every
possible combination of two items in an array and make a comparison of
the two (let's just say they pick the one they like better. I assume I
could use nested For-Next loops to do the work something but I'd like
input on three things:
1) Avoid repeating combinations, for instance: Compare A to B and later
B to A
2) Avoid comparisons to self: Compare A to A
3) Randomize comparisons. If using For-Next, it would first compare A
to all other options, then B to all other, then C, etc. How could I
skip around but be sure that All possible combinations are asked but
not repeated?

Thanks for the advice.

- John

Thanks for the suggestions. This is what I ended up with.
First I asked the user for number of choices and then collected the
string value for each choice and stored those in a range. (For
example: 3 choices: Vanilla, Chocolate, Strawberry)

Then, I created a table of index numbers in two columns to represent
the possible pairings. I used index numbers to represent the position
each string had in the original list (1 = Vanilla, 2 = Chocolate, 3 =
Strawberry)

Since I used index numbers instead of the strings, I was able to For -
Next statements and in If statement to ensure my list did not contain
duplicates or pairs where both items were the same.

Once the list table was created, I added a column of random numbers and
sorted.

In another procedure, I've pulled the table of index numbers in their
random order into array and the string values for the choices into
another array. With Looping and InputBox, I've been able to cycle
through the choices and record the "winner" of each pairing.

Here's the code for creating that initial table.

Thanks for the suggestions.

- John

----------------------------------------------------------------------------------------------------------

Sub SetupChoices()

Dim r As Integer ' Row index
Dim c As Integer ' Column index
Dim n As Integer ' Number of Choices
Dim d As Integer ' Data counter
Dim sh As Worksheet
Dim strChoice As String

Set sh = Worksheets("Data")
sh.Range("2:65000").Clear 'clear previous entries

' Collect the list of possible choices
n = InputBox("How many choices?")
For i = 1 To n
strChoice = InputBox("Enter choice " & i)
sh.Cells(i + 1, 8).Value = strChoice
Next i

' Fill the data table with unique combinations of index numbers in two
columns
d = 1

For r = 1 To n
For c = r + 1 To n
If r < c Then ' Don't add pairs where both are same
sh.Cells(d + 1, 1).Value = Rnd 'Random Number
sh.Cells(d + 1, 2).Value = r 'Index for choice 1 of pair
sh.Cells(d + 1, 3).Value = c 'Index for choice 2 of pair
d = d + 1
End If
Next c
Next r

'Sort by Random Number Colum
sh.Range("A1:C10000").Sort _
Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


MsgBox "Setup Complete"

End Sub