# Valid arrangement of non duplicate 1:1 pairs in a row

#### tschatz

##### New Member
Hello,

given the following data pairs:

A-B
A-C
A-D
A-E
A-F
B-C
B-D
B-E
B-F
C-D
C-E
C-F
D-E
D-F
E-F

with n = 6 being the number of unique single entities. I want to calculate one possible solution for the highest possible parallelisation.

Assuming each element can only exist once at a certain time, I can determine the maximum number of parallel slots as
Excel Formula:
``=ROUNDDOWN(n/2,0)``
.
I would like to find a possible valid arrangement of the data pairs.

One possible valid (every element is only allowed once per row) solution might look like this:
 B - C D - F A - E C - E A - D B - F A - B D - E C - F A - C B - D E - F A - F B - E C - D

I thought about randomly filling each row with the "left over" data-pairs starting with row 1 and checking their validity, however starting with row 3 you quickly run into solutions where not all columns can be filled out e.g.

 B - C D - F A - E C - E A - D B - F A - B C - D E - F A - C B - D A - F D - E B - E C - F

Can anyone give me a hint of how to best approach this topic?

Thanks a lot!

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Map1.xlsm
ABCDE
1A-B0,8481380,9089660,530637
2A-C0,4580890,9982030,085773
3A-D0,1227520,9896690,491893
4A-E0,205070,7880260,605936
5A-F0,9431090,6453820,849694
6B-C
7B-DB-CA-EC-D
8B-EC-FA-BE-F
9B-FD-FA-CC-E
10C-DD-EB-DB-F
11C-EA-DB-EA-F
12C-F
13D-E
14D-F
15E-F
Cell Formulas
RangeFormula
C1:E5C1=RANDARRAY(5,3)
C7:E11C7=INDEX(\$A\$1:\$A\$15,RANK(C1,\$C\$1:\$E\$5))
Dynamic array formulas.

Map1.xlsm
ABCDE
1A-B0,8481380,9089660,530637
2A-C0,4580890,9982030,085773
3A-D0,1227520,9896690,491893
4A-E0,205070,7880260,605936
5A-F0,9431090,6453820,849694
6B-C
7B-DB-CA-EC-D
8B-EC-FA-BE-F
9B-FD-FA-CC-E
10C-DD-EB-DB-F
11C-EA-DB-EA-F
12C-F
13D-E
14D-F
15E-F
Cell Formulas
RangeFormula
C1:E5C1=RANDARRAY(5,3)
C7:E11C7=INDEX(\$A\$1:\$A\$15,RANK(C1,\$C\$1:\$E\$5))
Dynamic array formulas.
Hi BSALV,

For example the this row:

the letter "C" is not unique.

a little bit more complicated, in average, 4 trials for 1 good parallelisation.
VBA Code:
``````Sub rand()
Dim rand2(), result()
a = ActiveSheet.Range("A1").CurrentRegion.Columns(1)
Application.ScreenUpdating = False
Do
DoEvents
ptr = ptr + 1
ReDim result(1 To 5)
rand1 = WorksheetFunction.RandArray(UBound(a))

ReDim rand2(1 To UBound(rand1), 1 To 2)
For i = 1 To UBound(rand2)
rand2(i, 1) = rand1(i, 1): rand2(i, 2) = a(i, 1)
Next

r = 1
b = True
Do
rand3 = Application.Sort(rand2, 1)
rand2 = rand3
For i = 1 To UBound(rand2)
b = (rand2(i, 1) < 1)
If Not b Then Exit For
sp = Split(rand2(i, 2), "-")
If InStr(result(r), sp(0)) + InStr(result(r), sp(1)) = 0 Then
result(r) = result(r) & "," & rand2(i, 2)
rand2(i, 1) = 1000000000#
If Len(result(r)) > 10 Then
result(r) = Mid(result(r), 2): r = r + 1:
Exit For
End If
End If
Next
Loop While b

With Range("D1").Resize(UBound(result))
.Value = Application.Transpose(result)
.TextToColumns .Range("A1"), xlDelimited, , , , , 1
End With

Loop While Range("D5") = "" And ptr < 100

Range("F6").Value = ptr & " trials"
Range("H" & Rows.Count).End(xlUp).Offset(1) = ptr
End Sub``````
Map1.xlsm
ABCDEF
1A-B1B-EC-DA-F
2A-C1B-FA-DC-E
3A-D1C-FA-BD-E
4A-E1A-CB-DE-F
5A-F1B-CD-FA-E
6B-C15 trials
7B-D1
8B-E1
9B-F1
10C-D1
11C-E1
12C-F1
13D-E1
14D-F1
15E-F1
Cell Formulas
RangeFormula
B1:B15B1=COUNTIF(\$D\$1:\$F\$5,A1)

Hi BSALV,

that gave me a very good starting point! Thanks a lot for your help!

BR
Tillmann

Replies
15
Views
244
Replies
10
Views
219
Replies
0
Views
153
Replies
3
Views
87
Replies
5
Views
103

1,203,535
Messages
6,055,964
Members
444,839
Latest member
laurajames

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back