Lookup Result Needed

[PM1]

Hi, I have a table below that lists 17 people who play with different people over 4 days

Pl/Day 1 2 3 4
A N I M H
B M J L I
C L K J O
D K L I M
E J M K N
F I N H K
G H O E P
H G P F Q
I F Q G L
J E A D B
K D C O A
L C B A D
M B D Q C
N A E B F
O Q F P E
P O G Q J
Q P H C G


From the above table how can I construct a lookup table that reports a playing table as below:

A B C D E F G H I J K L M N
A 4 2 3 1
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q

 

[Andrew Poulsom]

If your first table is in the range A1:E18 on Sheet 1 and your second table is in the range A1:R18 on another sheet, in B2 enter:

=IF(COUNTIF(INDEX(Sheet1!$B$2:$E$18,MATCH($A2,Sheet1!$A$2:$A$18),0),B$1),INDEX(Sheet1!$B$1:$E$1,MATCH(B$1,INDEX(Sheet1!$B$2:$E$18,MATCH($A2,Sheet1!$A$2:$A$18),0),FALSE)),"")

and copy down and across.

 

[PM1]

Many Thanks,

Always knew the Masters would come through

 

[PM1]

Andrw,

Is there a way I can randomise the first table. At present I constructed it by trial an error without any real logic. If I need to change it around I then stuggle with the rest of the table.

 

[Andrew Poulsom]

What do you mean by randomise? If you mean have it with column A unsorted, change the formula to:

=IF(COUNTIF(INDEX(Sheet1!$B$2:$E$18,MATCH($A2,Sheet1!$A$2:$A$18,FALSE),0),B$1),INDEX(Sheet1!$B$1:$E$1,MATCH(B$1,INDEX(Sheet1!$B$2:$E$18,MATCH($A2,Sheet1!$A$2:$A$18,FALSE),0),FALSE)),"")

 

[PM1]

This is for Table 1, the formula you gave me for table 2 is fine.

As I stated, in Table 1 I have manually alloctaed the letters that appear. It is those letters that i want generated in that no letter can be associated with the same letter twice

 

[Andrew Poulsom]

Sorry, I don't know how to do that.

 

[PM1]

OK - Lets see if this works. Here is the table I have

Excel Workbook
	H	I	J	K	L	M	N	O	P
1	*	*	*	*	Player/Day	Th	Fri am	Fri pm	Sat
2	A	Alex Hickson	13	*	A	*	*	*	*
3	B	Roy Button	21	*	B	*	*	*	*
4	C	Alaistair Pinney	28	*	C	*	*	*	*
5	D	Andy Ockendon	25	*	D	*	*	*	*
6	E	Morris Gwilt	23	*	E	*	*	*	*
7	F	Dave Creak	28	*	F	*	*	*	*
8	G	Andy Parfitt	13	*	G	*	*	*	*
9	H	Howell Roberts	12	*	H	*	*	*	*
10	I	Steve Milner	23	*	I	*	*	*	*
11	J	Hugh Laurie	24	*	J	*	*	*	*
12	K	Andrew Emmett	20	*	K	*	*	*	*
13	L	Martin Desmond	26	*	L	*	*	*	*
14	M	Stewart Parfitt	15	*	M	*	*	*	*
15	N	Steve Berg	31	*	N	*	*	*	*
16	O	Robert Bertram	21	*	O	*	*	*	*
17	P	Giles Marshall	20	*	P	*	*	*	*
18	Q	John Chittock	11	*	Q	*	*	*	*
Sheet3

I want to randomly generate a schedule in M2:P18 in that no player is aligned with another player more than once.

I've tried Random but that doesn't seem to work, not sure on what else does

 

[PM1]

Hi,

Andrew-R come up with this solution:-

Sub BuildSchedule()

Const lTEES = 17
Const lPLAYERS = 17
Const lFOURSLOTS = 8

Const lFIRST_DATA_ROW = 1
Const lNAME_COL = 6
Const lCOUNT_COL = 7
Const lFIRST_SLOT_COL = 2

Dim lSlots As Long
Dim lTeeLoop As Long
Dim lSlotLoop As Long
Dim rngCountRange As Range
Dim bSlotFilled As Boolean
Dim lInnerLoop As Long
Dim sRowValue(1 To lTEES) As String
Dim sPlayer As String
Dim lCheckLoop As Long
Dim bComboExists As Boolean
Dim lInnerCheckLoop As Long
Dim sToken As String

With ActiveSheet

Set rngCountRange = .Range(.Cells(lFIRST_DATA_ROW, lCOUNT_COL), .Cells(lFIRST_DATA_ROW + lPLAYERS - 1, lCOUNT_COL))

For lTeeLoop = 1 To lTEES

sRowValue(lTeeLoop) = ""

If lTeeLoop <= lTEES - lFOURSLOTS Then
lSlots = 3
Else
lSlots = 4
End If

For lSlotLoop = 1 To lSlots

bSlotFilled = False
lInnerLoop = 1

While Not bSlotFilled And lInnerLoop <= lPLAYERS
sPlayer = .Cells(lFIRST_DATA_ROW + lInnerLoop - 1, lNAME_COL).Value
sToken = Chr(64 + lInnerLoop)
If rngCountRange.Rows(lInnerLoop).Value = Application.WorksheetFunction.Min(rngCountRange) Then
If lSlotLoop = 1 Or lTeeLoop = 1 Then
.Cells(lFIRST_DATA_ROW + lTeeLoop - 1, lFIRST_SLOT_COL + lSlotLoop - 1).Value = sPlayer
sRowValue(lTeeLoop) = sRowValue(lTeeLoop) & sToken
bSlotFilled = True
Else
bComboExists = False
lCheckLoop = 1

While Not (bComboExists) And lCheckLoop < lTeeLoop
For lInnerCheckLoop = 1 To Len(sRowValue(lTeeLoop))
If InStr(sRowValue(lCheckLoop), Mid(sRowValue(lTeeLoop), lInnerCheckLoop, 1)) > 0 And InStr(sRowValue(lCheckLoop), sToken) > 0 Then
bComboExists = True
Else
'lCheckLoop = lCheckLoop + 1
End If
Next lInnerCheckLoop
lCheckLoop = lCheckLoop + 1
Wend

If Not bComboExists Then
.Cells(lFIRST_DATA_ROW + lTeeLoop - 1, lFIRST_SLOT_COL + lSlotLoop - 1).Value = sPlayer
sRowValue(lTeeLoop) = sRowValue(lTeeLoop) & sToken
bSlotFilled = True
Else
lInnerLoop = lInnerLoop + 1
End If
Application.Calculate
End If
Else
lInnerLoop = lInnerLoop + 1
End If
Wend
Next lSlotLoop
Next lTeeLoop

End With

End Sub

Problem is that it allows for duplicates in the columns, can anybody amend to stop this?

 

[PM1]

This was the table that I used to test the above Macro

Excel Workbook
	A	B	C	D	E	F	G
1	1	Mike	Eric	Andy		Andy	4
2	2	John	Charles	Andy		Brian	4
3	3	George	Dave	Oscar		Charles	4
4	4	Fred	Oscar	Ian		Dave	4
5	5	Eric	George	Brian		Eric	4
6	6	Mike	Brian	Peter		Fred	3
7	7	Andy	Ian	Dave		George	3
8	8	John	Norm	Eric		Henry	4
9	9	Charles	Dave	Henry		Ian	4
10	10	Charles	Kevin	Eric	Fred	John	4
11	11	George	John	Henry	Mike	Kevin	3
12	12	Quentin	Ian	John	Kevin	Liam	4
13	13	Norm	Andy	Brian	Quentin	Mike	3
14	14	Charles	Peter	Ian	Liam	Norm	2
15	15	Kevin	Dave	Liam	Liam	Oscar	3
16	16	Oscar	Peter	Henry	Quentin	Peter	3
17	17	Fred	Brian	Henry	Liam	Quentin	3
Sheet1