League table automatic sort/update

Thanks for all your help so far, the problem with being new to this
stuff is figuring out how to use all the available information. I have
one more query as described below, then I'll be ready to go and have a
good play with all this stuff !

I'd like to implements a match results table as shown below

TeamA ScA ScB TeamB MatchID
Germany 2 1 Costa Rica FR1
Poland 2 2 Ecuador FR1
England 3 1 Paraguay FR1
Germany 4 2 Ecuador FR2
Costa Rica 1 1 Poland FR2

As the results table above is updated I'd like to summarize the
results for each team in another worksheet/table. Basically for each
match teams is assigned 1 point for a win, 0.5 points for a draw and 0
points if they lose a match. For the example results table the summary
table would look like this:

Team FR1 FR2 FR3
Costa Rica 0 0.5
Germany 1 1
Ecuador 0.5 0
England 1
Poland 0.5 0.5

Is this something that can be done with formulas or would it be easier
to write some VBA code in response to changes in the original match
results table.

Thx
RH

Assuming the data is in A1:E20

Put the teams in J2:Jn, FR1 in K1, Fr2 in K2, etc., then in K2

=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))
+
SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+
SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2

copy down and across

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
oups.com...
Thanks for all your help so far, the problem with being new to this
stuff is figuring out how to use all the available information. I have
one more query as described below, then I'll be ready to go and have a
good play with all this stuff !

I'd like to implements a match results table as shown below

TeamA ScA ScB TeamB MatchID
Germany 2 1 Costa Rica FR1
Poland 2 2 Ecuador FR1
England 3 1 Paraguay FR1
Germany 4 2 Ecuador FR2
Costa Rica 1 1 Poland FR2

As the results table above is updated I'd like to summarize the
results for each team in another worksheet/table. Basically for each
match teams is assigned 1 point for a win, 0.5 points for a draw and 0
points if they lose a match. For the example results table the summary
table would look like this:

Team FR1 FR2 FR3
Costa Rica 0 0.5
Germany 1 1
Ecuador 0.5 0
England 1
Poland 0.5 0.5

Is this something that can be done with formulas or would it be easier
to write some VBA code in response to changes in the original match
results table.

Thx
RH

Liked your much neater approach, Bob,
but I only managed to get the results in J1:L6 as

Team FR1 FR2
Costa.. 0 1
Germany 1 1
Ecuador 0 0
England 1 0
Poland 1 0

I placed your suggested formula* in K2, and copied across/down to L6:
=SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2

*slightly adapted the ranges

Any tweak possible to your suggested formula
which would drive out the OP's desired results ?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
Assuming the data is in A1:E20

Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc., then in K2

=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))
+
SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+
SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2

copy down and across

Just another formulas play to try ..

Assuming this table is in a sheet: X, in A1:E6

TeamA ScA ScB TeamB MatchID
Germany 2 1 Costa Rica FR1
Poland 2 2 Ecuador FR1
England 3 1 Paraguay FR1
Germany 4 2 Ecuador FR2
Costa Rica 1 1 Poland FR2

Put in F2: =IF(OR(B2="",C2=""),"",IF(B2C2,1,IF(B2C2,0,0.5)) )
Put in G2: =1-F2
Select F2:G2, copy down to G6

Then in another sheet: Y (say)
you have the summary table set up within A1:C6

Team FR1 FR2
Costa Rica
Germany
Ecuador
England
Poland

Put in the formula bar for B2 and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER, instead of just pressing ENTER:

=IF(ISNA(MATCH(1,(X!$A$2:$A$6=$A2)*(X!$E$2:$E$6=B$ 1),0)),IF(ISNA(MATCH(1,(X!$D$2:$D$6=$A2)*(X!$E$2:$ E$6=B$1),0)),"",INDEX(X!$G$2:$G$6,MATCH(1,(X!$D$2: $D$6=$A2)*(X!$E$2:$E$6=B$1),0))),INDEX(X!$F$2:$F$6 ,MATCH(1,(X!$A$2:$A$6=$A2)*(X!$E$2:$E$6=B$1),0)))

Copy B2 across/down to C6 to populate

Above will yield the desired results:
Team FR1 FR2
Costa Rica 0 0.5
Germany 1 1
Ecuador 0.5 0
England 1
Poland 0.5 0.5

(Cell C5, ie England-FR2 will return as a "blank":"")
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"RedHook" wrote:
Thanks for all your help so far, the problem with being new to this
stuff is figuring out how to use all the available information. I have
one more query as described below, then I'll be ready to go and have a
good play with all this stuff !

I'd like to implements a match results table as shown below

TeamA ScA ScB TeamB MatchID
Germany 2 1 Costa Rica FR1
Poland 2 2 Ecuador FR1
England 3 1 Paraguay FR1
Germany 4 2 Ecuador FR2
Costa Rica 1 1 Poland FR2

As the results table above is updated I'd like to summarize the
results for each team in another worksheet/table. Basically for each
match teams is assigned 1 point for a win, 0.5 points for a draw and 0
points if they lose a match. For the example results table the summary
table would look like this:

Team FR1 FR2 FR3
Costa Rica 0 0.5
Germany 1 1
Ecuador 0.5 0
England 1
Poland 0.5 0.5

Is this something that can be done with formulas or would it be easier
to write some VBA code in response to changes in the original match
results table.

Thx
RH

Thanks Max, I forgot the half-score for column D teams.

=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))
+
SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+
SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2+
SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Max" wrote in message
...
Liked your much neater approach, Bob,
but I only managed to get the results in J1:L6 as

Team FR1 FR2
Costa.. 0 1
Germany 1 1
Ecuador 0 0
England 1 0
Poland 1 0

I placed your suggested formula* in K2, and copied across/down to L6:

=SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUM
PRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPROD
UCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2

*slightly adapted the ranges

Any tweak possible to your suggested formula
which would drive out the OP's desired results ?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
Assuming the data is in A1:E20

Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc.,
then in K2


=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))
+

SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+

SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2

copy down and across

Can even simplify it G

=SUMPRODUCT(--((($A$1:$A$20=$J3)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J3)*
($B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+
SUMPRODUCT(--(($A$1:$A$20=$J3)+($D$1:$D$20=$J3)),--($E$1:$E$20=K$1),--($B$1:
$B$20=$C$1:$C$20))/2

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Max" wrote in message
...
Liked your much neater approach, Bob,
but I only managed to get the results in J1:L6 as

Team FR1 FR2
Costa.. 0 1
Germany 1 1
Ecuador 0 0
England 1 0
Poland 1 0

I placed your suggested formula* in K2, and copied across/down to L6:

=SUMPRODUCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUM
PRODUCT(--($D$1:$D$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6$C$1:$C$6))+SUMPROD
UCT(--($A$1:$A$6=$J2),--($E$1:$E$6=K$1),--($B$1:$B$6=$C$1:$C$6))/2

*slightly adapted the ranges

Any tweak possible to your suggested formula
which would drive out the OP's desired results ?

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
Assuming the data is in A1:E20

Put the teams in J2:Jn, FR1 in K1, Fr2 in L1 [typo corrected], etc.,
then in K2


=SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))
+

SUMPRODUCT(--($D$1:$D$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+

SUMPRODUCT(--($A$1:$A$20=$J2),--($E$1:$E$20=K$1),--($B$1:$B$20=$C$1:$C$20))/
2

copy down and across

Excellent - Thanks again for your help. Do you generate these complex
formulas 'by hand' or is there some tool/trick to help you generate
them ?

No, it's all by hand mate. I did create a tool once, and whilst it is useful
to help get the syntax correct, you still need to know the basics so as to
know what to ask for.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"RedHook" wrote in message
oups.com...
Excellent - Thanks again for your help. Do you generate these complex
formulas 'by hand' or is there some tool/trick to help you generate
them ?

Bob, thanks .. albeit I had to drop that into K3 though
before propagating it across/down and up! g

Just a lingering point though:
England - FR2 will return a zero,
instead of a "blank" (part of the OP's desired result?)

I'm not sure how important the above is to the OP (or to the underlying
beautiful game) to have the result returned as a zero when presumably England
has yet to play the game under MatchID FR2 ??
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bob Phillips" wrote:
Can even simplify it G

=SUMPRODUCT(--((($A$1:$A$20=$J3)*($B$1:$B$20$C$1:$C$20))+(($D$1 :$D$20=$J3)*
($B$1:$B$20$C$1:$C$20))),--($E$1:$E$20=K$1),--($B$1:$B$20$C$1:$C$20))+
SUMPRODUCT(--(($A$1:$A$20=$J3)+($D$1:$D$20=$J3)),--($E$1:$E$20=K$1),--($B$1:
$B$20=$C$1:$C$20))/2

fwiw, a slight correction .. line:
Put in G2: =1-F2

should read:
Put in G2: =IF(F2="","",1-F2)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---