Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

EXCEL CONUNDRUM

Posted on 2014-09-01
8
Medium Priority
?
110 Views
Last Modified: 2014-09-04
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
Comment
Question by:Jagwarman
  • 5
  • 2
8 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40296602
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

by:Jagwarman
ID: 40296628
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

by:Jagwarman
ID: 40298107
Hi jell, will you be able to help me out with this ?

Thanks
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 6

Accepted Solution

by:
johnb25 earned 1500 total points
ID: 40299878
Hi,

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

John
Westborough.xlsx
0
 

Author Comment

by:Jagwarman
ID: 40300306
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

by:johnb25
ID: 40300681
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

by:Jagwarman
ID: 40300702
Hi johnb25

Unfortunately that does not work :-(

E 'Martin' is in Rink 4 Four times
0
 

Author Closing Comment

by:Jagwarman
ID: 40302838
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question