How to Create a Cross Fixtures Table from a list of Results

[murphydon]

Hi There,

New to this site so be gentle please.

I am involved in a local sports league in Ireland.

I have a list of results like..


Home Team Home Team Score Away Team Score Away Team

Team A 1 1 Team B

Team C 2 3 Team D

Team C 0 1 Team A

Team D 3 1 Team B

... etc


What I want to do is to present this result data in a cross table format like..

Team A Team B Team C Team D

Team A XXX 1-1

Team B XXX

Team C 0-1 XXX 2-3

Team D 3-1 XXX


Hope you get the idea.

Thats where I'm stuck. Im familiar enough with matches / index / etc but, for the life of me, I cant figure out how to get this to work. I can see how standard Pivot Tables would get a similar result but this would require that the home and away score in the pivot tables would end up on different lines / rows.


Hope someone can help me out.


Please note that when I looked at this post live the spaces used to "tab" the tables were not showing. I can but hope that you can understand the requirement


Thanks again

 

[Hui]

Murphydon


Firstly, Welcome to the Chandoo.org Forums


Have a look at:

https://www.dropbox.com/s/0fh92lzlu7dkh0i/Cross%20Fixture.xlsx

 

[murphydon]

Hey Hui,


That's perfect. Thanks heaps

 

[murphydon]

Not sure if Im allowed a supplemental but...


A much simpler aspect of the same problem...

Your solution elegantly leaves blank any fixtures which have not happened yet but also leaves them blank if the result happens to be 0-0.

I presume that the this has something to do with zero values not showing or something like that.

What I was wondering was if there is a more foolproof solution to the 0-0 v "not played yet" conundrum


TIA

 

[NARAYANK991]

Hi Murphy ,


I am sure Hui will respond , but in the meantime , if you wish to try this modification , enter the following array formula in cell G7 , and copy across and down :

[pre]

=IFERROR(IF(G$6=$F7,"",IF(MATCH(G$6&$F7,$A$2:$A$5&$D$2:$D$5,0)>0,SUMPRODUCT(($A$2:$A$5=G$6)*($D$2:$D$5=$F7)*($B$2:$B$5))&" - "&SUMPRODUCT(($A$2:$A$5=G$6)*($D$2:$D$5=$F7)*($C$2:$C$5)))),"Not Played")

[/pre]
This will ensure that 0 - 0 results are filled in. Of course , with this change , you will have to remove the conditional formatting that was inserted for the cells in the table.


Narayan

 

[Hui]

I choose not to use a Formula for the 0-0 results but allow a 0-0 result and then use Conditional formatting to hide the result.


It kept the whole project simpler.

 

[bobhc]

Good day

I think that Hui's way is a neat and tidy sheet but if you want to view 00 then edit the rules in CF