Unique draws "Football"

[Michael]

I have problem that is driving me nuts, I confess I do not have the
skills to solve. I want to find the how many teams Aston Villa has
done a double draw over the season.

=IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40=BF3)*($BF$3:$BF$40=$D3)*($BE$3:$BE$40=$BG$3:$BG$40)*((BP3="d")*(BQ3="d")),1))),$BF3,"")
With CRTL+ALT+ENTER.

I get the correct answers in this case Chelsea. Then I get Fulani and
Middlebrow in has only played Aston Villa once.


In row 2 column BD:BS are the header columns
The data is in BD3:BD40
HS = Home Score, AS = Away Score
Hr =Home Result (I.e W, D, or L) Column BP
AR =Away Result (I.e wd, d, or dl) Column BQ

The format has shifted. the data is the same with until the solid line
below as the games have not been played as yet.

BD BE BF BG Team HS Team AS P W
D L pp wd d dl Hr AR Hg Aged
Arsenal 1 Aston Villa 1 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 2 Reading 1 1 1 0 0 1
0 0 1 W l 1 -1
Aston Villa 2 Newcastle 0 1 1 0 0
1 0 0 1 W l 2 -2
West Ham 1 Aston Villa 1 1 0 1 0
1 0 1 0 D d 0 0
Watford 0 Aston Villa 0 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 2 Charlton 0 1 1 0 0
1 0 0 1 W l 2 -2
Chelsea 1 Aston Villa 1 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 1 Tottenham 1 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 1 Fulham 1 1 0 1 0 1
0 1 0 D d 0 0
Liverpool 3 Aston Villa 1 1 1 0 0
1 0 0 1 W l 2 -2
Aston Villa 2 Blackburn 0 1 1 0 0
1 0 0 1 W l 2 -2
Everton 0 Aston Villa 1 1 0 0 1 1
1 0 0 L w -1 1
Wigan 0 Aston Villa 0 1 0 1 0 1
0 1 0 D d 0 0
Aston Villa 1 Middlesbrough 1 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 1 Man City 3 1 0 0 1
1 1 0 0 L w -2 2
Portsmouth 2 Aston Villa 2 1 0 1 0
1 0 1 0 D d 0 0
Sheff Utd 2 Aston Villa 2 1 0 1 0
1 0 1 0 D d 0 0
Aston Villa 0 Bolton 1 1 0 0 1 1
1 0 0 L w -1 1
Aston Villa 0 Man Utd 3 1 0 0 1 1
1 0 0 L w -3 3
Tottenham 2 Aston Villa 1 1 1 0 0
1 0 0 1 W l 1 -1
Charlton 2 Aston Villa 1 1 1 0 0
1 0 0 1 W l 1 -1
Aston Villa 0 Chelsea 0 1 0 1 0 1
0 1 0 D d 0 0
Man Utd 3 Aston Villa 1 1 1 0 0 1
0 0 1 W l 2 -2
Aston Villa 2 Watford 0 1 1 0 0 1
0 0 1 W l 2 -2
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Newcastle 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 West Ham 0 0 0 0 0
0 0
Reading 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Arsenal 0 0 0 0 0
0 0
Fulham 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Liverpool 0 0 0 0 0
0 0
Aston Villa 0 Everton 0 0 0 0 0
0 0
Blackburn 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Wigan 0 0 0 0 0
0 0
Middlesbrough 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Portsmouth 0 0 0 0 0
0 0
Man City 0 Aston Villa 0 0 0 0 0
0 0
Aston Villa 0 Sheff Utd 0 0 0 0 0
0 0
Bolton 0 Aston Villa 0 0 0 0 0
0 0




Thanks in advance

 

[Bob Phillips]

I assume that AE2 holds the team being checked, say Aston Villa, but what is
in D240?

Maybe try this alternative

=IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40=BF3)*($BF$3:$BF$40=$AE$2)*
($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"")

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Just seen the point of another test, so a slight adjustment

=IF(AND($BD3=$AZ$1,$BE3=$BG3,MIN(IF(($BD$3:$BD$40=BF3)*($BF$3:$BF$40=$AZ$1)*
($BE$3:$BE$40=$BG$3:$BG$40)*($BQ$3:$BQ$40="d"),1))),$BF3,"")

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Michael]

I assume that AE2 holds the team being checked, say Aston Villa, but what is
in D240?

Maybe try this alternative

=IF(AND($BD3=$AE$2,$BE3=$BG3,MIN(IF(($BD$3:$BD$40=BF3)*($BF$3:$BF$40=$AE$2)*
($BE$3:$BE$40=$BG$3:$BG$40),1))),$BF3,"")

Bob,
I must apologise for the formatting as it looks like a right dog's
dinner.

In D340 are the home team this is duplicated to BD3:BD40. E3:E40
duplicate to BE3:BE40 Home Score. As the MIN function does not like
blanks cells I used the formula =IF(ISNUMBER(E3),E3,0).

The above formula produced
Middlesbrough
Portsmouth
Sheff Utd
West Ham
Chelsea
Arsenal
Fulham
Wigan

Chelsea is the only team that Aston Villa drawn home and away.
The others have play Aston Villa only once and drew.


The second formula:
=IF(AND($BD3=$AZ$1,$BE3=$BG3,MIN(IF(($BD$3:$BD$40=BF3)*($BF$3:$BF$40=$AZ$1)*
($BE$3:$BE$40=$BG$3:$BG$40)*($BQ$3:$BQ$40="d"),1))),$BF3,"")

Produced the following.

Portsmouth
Sheff Utd
West Ham
Chelsea
Arsenal
Wigan

However in this example is outputting the away teams that have played
as of yet.

Chelsea is the only team that Aston Villa drawn home and away.
The others have play Aston Villa only once and drew.

I hope that this makes sense.

Thanks

Michael

 

[Bob Phillips]

It only showed Chelsea for me.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Michael]

It only showed Chelsea for me.

Bob,
From your formula AZ1 = Aston Villa.
out of curiosity what results did you get form the first formula?

This is driving me nuts.

 

[Michael]

Bob,
From your formula AZ1 = Aston Villa.
out of curiosity what results did you get form the first formula?

This is driving me nuts.

Bob thanks I got it working by adding Bh3=1 bh is the column for
played game played. Thanks as this was so logical I missed the logic
in it.