Create matrix from larger table

Hi Guys,

For fun I've created this Round Robin schedule for up to 50 players based on a Berger Table.

The next step would be to create a matrix on Scoresheet Columns H & I with the results of the calculation on Berger_Table sheet.

Using VBA (which I've included for reference) it is easy to create the matrix but I was wondering if the same can be done using formulea.

Columns K & L are the results I'm looking for, Columns M & N are the VlookUps for these numbers based on Names list.

So changing the number of Players and clicking button will produce new matrix for reference.

Since I'm not a formula guy any suggestions or improvements on the formulas used so far is greatly appreciated.

PS: Only use even number of players.

Greetz

Rudi

Anyone wants to give this a try ?

Hi Rudi.

I'm willing. I just don't understand what I am looking at and I don't know enough VBA to get help from the macro.

I don't know what a Berger Table is and am unfamiliar with it's construction. I don't know if that is significant.

The only relationship that I think I understand in K:L is a ten column cycle that advances 2 rows with each cycle in the Berger table.
Then I am lost.

In G the venue numbers seem to have no roles or relationship to K:L or anything else. Unless you are expecting returns from only the first 3 cycles in the Berger table I discern no patterns that will reproduce K:L.

Can you help me out? What am I missing?

You can disregard Column G. That's something I'm working on seperately.
The more players you add in Names column the more rows and columns on Berger sheet become 'visible'. Let's say we have 14 players (n). Player 14 (n) has a fixed position in the table and all other player rotate clockwise n/2 positions.
To construct next round the last player, number 8 in the first round moves to the head of the table followed by number 9 against 7, 10 against 6, ...
The 3d round the last player, number 2 in second round moves to the head followed by number 3, ....Arithmetically, this equates to adding n/2 to the previous row, with the exception of player n. When the result of the addition is greater than (n−1), then subtract (n/2−1).

So what I'm after is to put every game of every round one below the other in column H & I without having blank rows in between. The challenge is that the number of 'visible' columns on Berger sheet changes according to the number of players.

The results from macro are all the games listed one below the other as I would like it done with formulas.

Hope this helps.

You can disregard Column G. That's something I'm working on seperately.

OK but will it be always be present?
As the Venue numbers currently repeat 3 times ... which is what Number of venues in D2 suggests ... I could make use of this as a "helper" column in the formula.

Also it may be helpful to build a dynamically calculated named range in Name Manager to handle the:

• changes in Number of venues in D2
• and the changing number of players

Is the use of such named ranges acceptable?
Are additional helper columns also acceptable in either/both sheets? (I am considering the wisdom of avoiding resource heavy array formulas.)

Edit Additionally just to be sure I have the concept please look at output I think you are wanting in H:I. These are manually entered. Do I have this much right?

The venue column is just a seperate problem I'm trying to resolve.
I'll try to explain with an example.
In a chess tournament I have 5 tables (the number I enter in D2). So game 1 goes to table 1, game 2 to table 2, until table 5. Then it jumps back to table 1 for game 6, table 2 for game 7 until all games are listed.
So when I enter a different number in D2 this sequence should change accordingly.
For the rest you can use or do whatever you think might help to reach a solution.
I'm just interested in the fact this can be resolved with formulas rather than VBA because there are people that haven't set up excel properly to use code so it would throw an error if they tried using the file.

In the attached find these dynamic named range formulas (DNRs) in Name Manager.
There is a copy for quick examination listed in O:P of 'ScoreSheet'

Berger_ACTIVE	=INDEX(Berger_Table!$C:$C,COUNTA(Names)*2):INDEX(Berger_Table!$3:$3,,MATCH(1E+306,Berger_Table!$3:$3,1))
Names	=ScoreSheet!$B$2:INDEX(ScoreSheet!$B:$B,MATCH("zzzzz",ScoreSheet!$B:$B,1))

Berger_ACTIVE
Defines the range that is visible in 'Berger_Table'. Try making the visible range larger/smaller. The DNR resizes to fit the data.

Names
Defines the range of names in 'ScoreSheet' column B.

In C2 is a helper cell. It just defines the number of columns (Venues?) by dividing number of Names by 2.

Formula:

=COUNTA(Names)/2

The formula for Player1/2 pairings is in H2 filled down and across.

Formula:

=IF(ROWS($A$2:$A2)>$C$2*$D$2,"",INDEX(Berger_ACTIVE,(CEILING(ROWS($A$2:$A2)/$C$2,1)-1)*2+1+(COLUMNS($H$1:H$1)-1),

MOD(ROWS($A$2:$A2)-1,$C$2)+1))

Dave, this is AWESOME !!!!!

Exactly what I was looking for. I only have removed the reference to venue column as it has no bearing on the results of Berger sheet.

I've also found a formula for Column G which does what I expected to see. Changing D2 changes sequence in Column G.

The only thing left is CF formula for column G so rows without visible results in columns H/I are formatted with white font color.

Thanks a million.

Regards

Rudi

You're welcome, Rudi.

Sorry it took me so long to get the concept and see the patterns. I am guessing from the workbook you already know how to do the CF formula.

Thanks for the feedback and added rep.

@ Dave

More than well deserved.