Extracting data from one table format and putting into a different viewing format

That probably isn't the best way to describe this problem but there isn't enough room....

Hello,

Please see the attachment for a full example of what I'm trying to accomplish.

I have a workbook (not the attachment) that creates a balanced round robin tournament schedule. The output of the schedule looks like my example in columns E, F and G with the Home and Away color coded using Excel ColorIndex colors. I have a Function built in a Module that allows me to use the ColorIndex in formulas, which is what I've been working with.

For ease of the players to understand the schedule as far as who they are playing and if they are the Home or Away team, I need to put the schedule into a more readable format, such as in my example in columns J through P. I'm able to populate the first match for each week however, I've had no luck populating any other matches for a week because none of the previous matched teams can be in them... You will see what I mean when you look at the example.

How can I do this?

Thanks for your help!

Hi Pete,

Thanks for the reply. I checked out your example and unfortunately it doesn't keep the matches Home and Away the way they are from the color coded columns.

The color coded columns:

There are three columns in the example, the 1st column is the list of teams that will be playing each other in weekly tournaments, the 2nd column represents who they each play in Week 1 matches and the 3rd represents who they play in the Week 2 matches. There will be additional weeks of play beyond these two allotting for the number of total weeks that will need to be played for each team to play each other team one time. All of them are read from left to right per the week. If the color of the cell adjacent to a team in column E for any specified Week is green, then the team in column E is at Home and plays the team in the specified Week column which would be the Away team. If the color of the cell is red, then the team in column E is the Away team and playing at the location of the Home team.

For instance, in the Week 1 table in columns J:L (in your example), the format of who is playing who and what their Home and Away status is should be as follows:

Match------Home-----------Away
1----------Columbia 1------Columbia 2
2----------Wigwam 2-------Columbia 3
3----------Columbia 4------Wigwam 1
4----------Kozy 2-----------Pour House 1
5----------Pour House 2----Kozy 1
6----------Pastime 2--------Pastime 1


Week 2 should look like this:

Match------Home-----------Away
1----------Columbia 3------Columbia 1
2----------Columbia 2------Columbia 4
3----------Kozy 1-----------Wigwam 2
4----------Wigwam 1-------Kozy 2
5----------Pastime 1--------Pour House 2
6----------Pour House 1----Pastime 2

As far as which match number they end up in for a week doesn't matter as long as the Home and Away structure always remains in tact. I included the example rotation above in your example file attached.

The problem I've been having is that once you enter in the formulas for teams that will play each other in Match 1, I can't just go down to the next team in column E and do the same because as it is for Week 1 for example, Columbia 1 and Columbia 2 are already playing in Match 1 so they can't repeat for Match 2. Same goes for all other matches 3 through 6. It seems like the formulas would need to be able to look at all of the teams for that week, identify which ones will be at Home and place them into the Home slots, and then look up which team they are playing that week and insert them into the Away slots adjacent to the correct team... I haven't been able to figure out a way to do that.

I've come up with a way to accomplish this, though it really isn't an elegant solution. The main problem was to be able to locate the green cells and apply the Home teams to their correct Home column cells in the alternate table format without them being repeated in any of the Home column cells. Once I figured that out, a simple INDEX and MATCH formula easily finds the Away teams associated with each Home team.

If your interested, see this updated attachment. The formulas are in the alternate tables in columns J:P and they are able to account for both an even and an odd number of teams.

I would still like to keep this post open for a little while longer in the hope that someone can lead me to a better way of doing this. My current way can't possibly be the only way, or the best way to do this.... It's a good challenge if you're interested. If you give it a shot, thank you very much!

For me, the whole "color" thing was a non-starter. "Color is not data" is one of the first things the gurus here in the forum pressed on me years and years ago, and they were right. Color is for the human eye. If you have some reason in your head for applying a certain color to a row or cell, that same logic can be added as DATA in the same row in some manner so anyone looking at your data instantly knows the same thing without colors.

So in your data I would have a column called HOME and one called AWAY, or I would have a cell on each row next to each opponent that says HOME or AWAY.

Or I would restructure the whole table so all HOME teams are in the first column, the AWAY team in the second and the WEEK number becomes a third column. This means more rows of data, certainly. But since you're just using that as a reference database and plan to restructure that into tables later, that's fine. And your tables become extremely easy to create in comparison to what you've started with.

My 4 cents.

Hi Jerry,

Thanks very much for your reply and comments. I do appreciate the feedback.

I agree with you about the colored cells, although my current formulas are turning lemons into lemon-aide pretty well. Unfortunately, the way the color coded data is produced is going to have to be the way I need to work with it. I found a balanced round robin creator (Ian Wakeling's) that uses only VBA to create the schedule and the outcome is the way you see it in my example in columns E:G. Any data present there previous to rerunning the schedule creator is wiped out. I can only wish I was as talented with VBA as Ian. I was able to shoehorn it into my own workbook where I track everything for my pool league. Also, unfortunately, I have a lot of older people on the league who most likely still have VCRs with the clock still blinking 12:00... If I don't spoon feed this to them it will be more trouble for me than it's worth. So, I really have to work with what I have available to me and port the auto-created schedule into another, more readable format in order to difuse any and all confusion. Since I intend to one day pass this along to some other poor stooge to do for the league, it all has to be as automated as much as possible so I never have to look at it again. Otherwise it would be no big deal for me to add some more columns in to the color coded version whenever I have to create a new schedule and use those along with helper columns, as you suggested.

Really, I'm more hoping for suggestions on any better way to write the formulas in the new tables or, someone with more knowledge than myself to say "nope, this is the best it can be".

Thanks again, Jerry.