How to pick Home and Away results from a list
 

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

How to pick Home and Away results from a list
 

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

Biff

wrote in message
oups.com...
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

How to pick Home and Away results from a list
 

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 wrote:
Need to see how the data is setup. Your description doesn't really provide
enough detail.

Biff

wrote in message
oups.com...
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

How to pick Home and Away results from a list
 

Ok.........

Assume this table is in the range A1:D6. A1:D1 are column headers. The
actual data in A2:D6:

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

wrote in message
oups.com...
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 wrote:
Need to see how the data is setup. Your description doesn't really
provide
enough detail.

Biff

wrote in message
oups.com...
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

How to pick Home and Away results from a list
 

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 wrote:
Ok.........

Assume this table is in the range A1:D6. A1:D1 are column headers. The
actual data in A2:D6:

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

wrote in message
oups.com...
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 wrote:
Need to see how the data is setup. Your description doesn't really
provide
enough detail.

Biff

wrote in message
oups.com...
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

How to pick Home and Away results from a list
 

You're welcome!

Biff

wrote in message
oups.com...
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 wrote:
Ok.........

Assume this table is in the range A1:D6. A1:D1 are column headers. The
actual data in A2:D6:

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

wrote in message
oups.com...
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 wrote:
Need to see how the data is setup. Your description doesn't really
provide
enough detail.

Biff

wrote in message
oups.com...
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