software

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

Biff

gt; wrote in message oups.com...
gt;I have a sheet showing the 46 results progressively over a season for
gt; my team (23 Home and 23 away fixtures)
gt; These are displayed in rows with columns showing opposition name, Home
gt; score, Away score and Venue (H(ome) or A(way))
gt; The Venues column will have random order E.G. H,A,H,H,A,H,A etc
gt;
gt; I would like to pick out from these 46 results the Home results and
gt; Away results and place them in separate lists automatically.
gt;
gt; Is there a formula to do this please.
gt;
gt; Thanks
gt; Neil
gt;
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:
gt; Need to see how the data is setup. Your description doesn't really provide
gt; enough detail.
gt;
gt; Biff
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt;I have a sheet showing the 46 results progressively over a season for
gt; gt; my team (23 Home and 23 away fixtures)
gt; gt; These are displayed in rows with columns showing opposition name, Home
gt; gt; score, Away score and Venue (H(ome) or A(way))
gt; gt; The Venues column will have random order E.G. H,A,H,H,A,H,A etc
gt; gt;
gt; gt; I would like to pick out from these 46 results the Home results and
gt; gt; Away results and place them in separate lists automatically.
gt; gt;
gt; gt; Is there a formula to do this please.
gt; gt;
gt; gt; Thanks
gt; gt; Neil
gt; gt;Ok.........

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

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

To extract the quot;Homequot; data enter this formula as an array using the key
combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Hquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Hquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)

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 quot;Awayquot; games and use the same formula but change
the references from quot;Hquot; to quot;Aquot;:

=IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Aquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Aquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)

Biff

gt; wrote in message oups.com...
gt; OK, sorry. I thought it might be self explanatory
gt; I have columns like this
gt; A B C D
gt; Team Home/Away For Against
gt; abc H 2 1
gt; cde A 1 3
gt; fgh H 1 1
gt; ijk H 2 0
gt; lmn A 1 1
gt;
gt; So, we played 'abc' at Home and won 2-1, then played 'cde' Away and
gt; lost 1-3 etc.
gt; As the season progresses, these lines fill to 46 in all as I input
gt; home/away results.
gt; I would like to extract these results to a separate area to show
gt; chronological Home results and likewise with Away results in the same
gt; format as above, to geventually give 2 columns of 23 results at seasons
gt; end.
gt;
gt; Neil
gt;
gt; Biff wrote:
gt;gt; Need to see how the data is setup. Your description doesn't really
gt;gt; provide
gt;gt; enough detail.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt;I have a sheet showing the 46 results progressively over a season for
gt;gt; gt; my team (23 Home and 23 away fixtures)
gt;gt; gt; These are displayed in rows with columns showing opposition name, Home
gt;gt; gt; score, Away score and Venue (H(ome) or A(way))
gt;gt; gt; The Venues column will have random order E.G. H,A,H,H,A,H,A etc
gt;gt; gt;
gt;gt; gt; I would like to pick out from these 46 results the Home results and
gt;gt; gt; Away results and place them in separate lists automatically.
gt;gt; gt;
gt;gt; gt; Is there a formula to do this please.
gt;gt; gt;
gt;gt; gt; Thanks
gt;gt; gt; Neil
gt;gt; gt;
gt;
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:
gt; Ok.........
gt;
gt; Assume this table is in the range A16. A11 are column headers. The
gt; actual data in A26:
gt;
gt; gt; A B C D
gt; gt; Team Home/Away For Against
gt; gt; abc H 2 1
gt; gt; cde A 1 3
gt; gt; fgh H 1 1
gt; gt; ijk H 2 0
gt; gt; lmn A 1 1
gt;
gt; To extract the quot;Homequot; data enter this formula as an array using the key
gt; combo of CTRL,SHIFT,ENTER:
gt;
gt; =IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Hquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Hquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)
gt;
gt; Copy across to 4 cells then down as needed. The output will look like this:
gt;
gt; abc H 2 1
gt; fgh H 1 1
gt; ijk H 2 0
gt;
gt; If you will have a total of 23 each, Home and Away, then copy the formula
gt; down 23 rows. Naturally, you'd have to adjust the range references to suit.
gt;
gt; Do the same thing for the quot;Awayquot; games and use the same formula but change
gt; the references from quot;Hquot; to quot;Aquot;:
gt;
gt; =IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Aquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Aquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)
gt;
gt; Biff
gt;
gt; gt; wrote in message
gt; oups.com...
gt; gt; OK, sorry. I thought it might be self explanatory
gt; gt; I have columns like this
gt; gt; A B C D
gt; gt; Team Home/Away For Against
gt; gt; abc H 2 1
gt; gt; cde A 1 3
gt; gt; fgh H 1 1
gt; gt; ijk H 2 0
gt; gt; lmn A 1 1
gt; gt;
gt; gt; So, we played 'abc' at Home and won 2-1, then played 'cde' Away and
gt; gt; lost 1-3 etc.
gt; gt; As the season progresses, these lines fill to 46 in all as I input
gt; gt; home/away results.
gt; gt; I would like to extract these results to a separate area to show
gt; gt; chronological Home results and likewise with Away results in the same
gt; gt; format as above, to geventually give 2 columns of 23 results at seasons
gt; gt; end.
gt; gt;
gt; gt; Neil
gt; gt;
gt; gt; Biff wrote:
gt; gt;gt; Need to see how the data is setup. Your description doesn't really
gt; gt;gt; provide
gt; gt;gt; enough detail.
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; gt; wrote in message
gt; gt;gt; oups.com...
gt; gt;gt; gt;I have a sheet showing the 46 results progressively over a season for
gt; gt;gt; gt; my team (23 Home and 23 away fixtures)
gt; gt;gt; gt; These are displayed in rows with columns showing opposition name, Home
gt; gt;gt; gt; score, Away score and Venue (H(ome) or A(way))
gt; gt;gt; gt; The Venues column will have random order E.G. H,A,H,H,A,H,A etc
gt; gt;gt; gt;
gt; gt;gt; gt; I would like to pick out from these 46 results the Home results and
gt; gt;gt; gt; Away results and place them in separate lists automatically.
gt; gt;gt; gt;
gt; gt;gt; gt; Is there a formula to do this please.
gt; gt;gt; gt;
gt; gt;gt; gt; Thanks
gt; gt;gt; gt; Neil
gt; gt;gt; gt;
gt; gt;You're welcome!

Biff

gt; wrote in message oups.com...
gt; Biff
gt;
gt; Brilliant, adapted it to suit actual references and some extra data
gt; (simplified it to get this help), but I got it working.
gt; Many thanks indeed
gt; Neil
gt;
gt; Biff wrote:
gt;gt; Ok.........
gt;gt;
gt;gt; Assume this table is in the range A16. A11 are column headers. The
gt;gt; actual data in A26:
gt;gt;
gt;gt; gt; A B C D
gt;gt; gt; Team Home/Away For Against
gt;gt; gt; abc H 2 1
gt;gt; gt; cde A 1 3
gt;gt; gt; fgh H 1 1
gt;gt; gt; ijk H 2 0
gt;gt; gt; lmn A 1 1
gt;gt;
gt;gt; To extract the quot;Homequot; data enter this formula as an array using the key
gt;gt; combo of CTRL,SHIFT,ENTER:
gt;gt;
gt;gt; =IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Hquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Hquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)
gt;gt;
gt;gt; Copy across to 4 cells then down as needed. The output will look like
gt;gt; this:
gt;gt;
gt;gt; abc H 2 1
gt;gt; fgh H 1 1
gt;gt; ijk H 2 0
gt;gt;
gt;gt; If you will have a total of 23 each, Home and Away, then copy the formula
gt;gt; down 23 rows. Naturally, you'd have to adjust the range references to
gt;gt; suit.
gt;gt;
gt;gt; Do the same thing for the quot;Awayquot; games and use the same formula but
gt;gt; change
gt;gt; the references from quot;Hquot; to quot;Aquot;:
gt;gt;
gt;gt; =IF(ROWS($1:1)lt;=COUNTIF($B$2:$B$6,quot;Aquot;),INDEX(A$2:A $6,SMALL(IF($B$2:$B$6=quot;Aquot;,ROW(A$2:A$6)-ROW(A$2) 1),ROWS($1:1))),quot;quot;)
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; gt; wrote in message
gt;gt; oups.com...
gt;gt; gt; OK, sorry. I thought it might be self explanatory
gt;gt; gt; I have columns like this
gt;gt; gt; A B C D
gt;gt; gt; Team Home/Away For Against
gt;gt; gt; abc H 2 1
gt;gt; gt; cde A 1 3
gt;gt; gt; fgh H 1 1
gt;gt; gt; ijk H 2 0
gt;gt; gt; lmn A 1 1
gt;gt; gt;
gt;gt; gt; So, we played 'abc' at Home and won 2-1, then played 'cde' Away and
gt;gt; gt; lost 1-3 etc.
gt;gt; gt; As the season progresses, these lines fill to 46 in all as I input
gt;gt; gt; home/away results.
gt;gt; gt; I would like to extract these results to a separate area to show
gt;gt; gt; chronological Home results and likewise with Away results in the same
gt;gt; gt; format as above, to geventually give 2 columns of 23 results at seasons
gt;gt; gt; end.
gt;gt; gt;
gt;gt; gt; Neil
gt;gt; gt;
gt;gt; gt; Biff wrote:
gt;gt; gt;gt; Need to see how the data is setup. Your description doesn't really
gt;gt; gt;gt; provide
gt;gt; gt;gt; enough detail.
gt;gt; gt;gt;
gt;gt; gt;gt; Biff
gt;gt; gt;gt;
gt;gt; gt;gt; gt; wrote in message
gt;gt; gt;gt; oups.com...
gt;gt; gt;gt; gt;I have a sheet showing the 46 results progressively over a season for
gt;gt; gt;gt; gt; my team (23 Home and 23 away fixtures)
gt;gt; gt;gt; gt; These are displayed in rows with columns showing opposition name,
gt;gt; gt;gt; gt; Home
gt;gt; gt;gt; gt; score, Away score and Venue (H(ome) or A(way))
gt;gt; gt;gt; gt; The Venues column will have random order E.G. H,A,H,H,A,H,A etc
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; I would like to pick out from these 46 results the Home results and
gt;gt; gt;gt; gt; Away results and place them in separate lists automatically.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Is there a formula to do this please.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Thanks
gt;gt; gt;gt; gt; Neil
gt;gt; gt;gt; gt;
gt;gt; gt;
gt;