Solved

# EXCEL CONUNDRUM

Posted on 2014-09-01
99 Views
this may not be possible but then again.

I have attached a spreadsheet that contains 3 grids.

The grid on the left contains letters A-X [representing players] and both Rinks and positions [Rinks across the top 1-4 Positions going down 1-3

The middle grid is made up of Names that corolate to the positions in the grid on the left

The grid on the right has Names and their respective letter

What I am trying to do is fill in the grid on the left so that

1) Each letter will end up in each rink once and two of the rinks twice [that is because there are 6 games and 4 rinks]

2) Each letter will be in each position twice [in either the top group of three [home team]  or bottom group of 3 [Away Team

3) Each letter [player] will play against different people where possible

in the attached file you will see that person K plays against person W in each game. This is what I am trying to avoid.

Is there a formula that will work this out or if not maybe an expert that can work it out for me.

Much appreciated.
Westborough.xlsx
0
Question by:Jagwarman

LVL 12

Expert Comment

Are there any other rules to the draw, or can opponents be randomly selected, with just the one rule of non-duplication of matches?

Rgds
0

Author Comment

Hi jell

not sure what you mean when you say one rule as there are 3 rules

1) Each letter [palyer] will end up in each rink [1-4 row 1] once and two of the rinks twice [that is because there are 6 games and 4 rinks]

2) Each letter [palyer] will be in each position twice [in either the top group of three [home team]  or bottom group of 3 [Away Team column A i.e.

1
2
3

1
2
3

3) Each letter [player] will play against different people where possible

These are the only rules

Thanks
0

Author Comment

Hi jell, will you be able to help me out with this ?

Thanks
0

LVL 6

Accepted Solution

Hi,

Try the attached.
No Formulas, just rearranged the patterns.

John
Westborough.xlsx
0

Author Comment

Hi johnb25

I understand the grid with the names and it looks great but I don't understand the grid on the left?

Thanks for your help I spen hours on it and could not solve it.
0

LVL 6

Expert Comment

Hi,

Attached is an updated grid.
I have placed the letters in the grid on the left, and used vlookups to put names in the middle grid.
The distribution across the rinks is not very even, but I think it minimises the rematches.

Hope this helps.

John
Westborough.xlsx
0

Author Comment

Hi johnb25

Unfortunately that does not work :-(

E 'Martin' is in Rink 4 Four times
0

Author Closing Comment

This one is a lot better than the one I did so I am going to accept this one. Thanks for all your help
0

## Featured Post

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.