EXCEL CONUNDRUM

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
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James ElliottManaging DirectorCommented:
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
JagwarmanAuthor Commented:
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
JagwarmanAuthor Commented:
Hi jell, will you be able to help me out with this ?

Thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

johnb25Commented:
Hi,

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

John
Westborough.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
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
johnb25Commented:
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
JagwarmanAuthor Commented:
Hi johnb25

Unfortunately that does not work :-(

E 'Martin' is in Rink 4 Four times
0
JagwarmanAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.