How to pick Home and Away results from a list

[neil40]

I have a sheet showing the 46 results progressively over a season for
my team (23 Home and 23 away fixtures)
These are displayed in rows with columns showing opposition name, Home
score, Away score and Venue (H(ome) or A(way))
The Venues column will have random order E.G. H,A,H,H,A,H,A etc

I would like to pick out from these 46 results the Home results and
Away results and place them in separate lists automatically.

Is there a formula to do this please.

Thanks
Neil

 

[Biff]

Need to see how the data is setup. Your description doesn't really provide
enough detail.

Biff

 

[neil40]

OK, sorry. I thought it might be self explanatory
I have columns like this
A B C D
Team Home/Away For Against
abc H 2 1
cde A 1 3
fgh H 1 1
ijk H 2 0
lmn A 1 1

So, we played 'abc' at Home and won 2-1, then played 'cde' Away and
lost 1-3 etc.
As the season progresses, these lines fill to 46 in all as I input
home/away results.
I would like to extract these results to a separate area to show
chronological Home results and likewise with Away results in the same
format as above, to geventually give 2 columns of 23 results at seasons
end.

Neil

 

[Biff]

Ok.........

Assume this table is in the range A16. A11 are column headers. The
actual data in A26:

A B C D
Team Home/Away For Against
abc H 2 1
cde A 1 3
fgh H 1 1
ijk H 2 0
lmn A 1 1

To extract the "Home" data enter this formula as an array using the key
combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$6,"H"),INDEX(A$2:A$6,SMALL(IF($B$2:$B$6="H",ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Copy across to 4 cells then down as needed. The output will look like this:

abc H 2 1
fgh H 1 1
ijk H 2 0

If you will have a total of 23 each, Home and Away, then copy the formula
down 23 rows. Naturally, you'd have to adjust the range references to suit.

Do the same thing for the "Away" games and use the same formula but change
the references from "H" to "A":

=IF(ROWS($1:1)<=COUNTIF($B$2:$B$6,"A"),INDEX(A$2:A$6,SMALL(IF($B$2:$B$6="A",ROW(A$2:A$6)-ROW(A$2)+1),ROWS($1:1))),"")

Biff

 

[neil40]

Biff

Brilliant, adapted it to suit actual references and some extra data
(simplified it to get this help), but I got it working.
Many thanks indeed
Neil

 

[Biff]

You're welcome!

Biff

Biff

Brilliant, adapted it to suit actual references and some extra data
(simplified it to get this help), but I got it working.
Many thanks indeed
Neil

 

[neil40]

Biff (or anyone)

I've got this working well, and have decided to use it on a more
comprehensive fixture list, which includes both League and Cup
fixtures.

There will be a two stage 'filter', firstly to pick out all League
fixtures, then to do the Home and Away 'filter'.
I can pick out the League matches using a similar formula to that which
you posted, BUT, there are lines missing in the resultant table/array -
the cup matches.
Is there a way to get round this so that all the lines are written one
after the other without gaps?
Current code to select the League matches is
{=IF(ROWS($17:17)<=COUNTIF($C$17:$C$67,"ELC"),INDEX(B$17:B$67,SMALL(IF($C$17:$C$67="ELC",ROW(B$17:B$67)-ROW(B$17)+1),ROWS($17:17))),"")}

Thanks again
Neil