Season Fixtures extracted

[jbloom17]

Hi All,

Someone please help, I am trying to create a spreadsheet with my clubs sports fixtures for the coming season.

The fixtures have been sent for the full league in the format below. I am trying to work a formula so that it will automatically extract the fixture for a set team and place it with the date on an additional tab.

[TABLE="width: 341"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19-Sep-15[/TD]
[/TR]
[TR]
[TD]Old Loughts 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]CoP 1[/TD]
[/TR]
[TR]
[TD]Wapping 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Chelmsford 1[/TD]
[/TR]
[TR]
[TD]St Albans 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Cambridge Uni 1[/TD]
[/TR]
[TR]
[TD]Ipswich 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Cambridge City 2[/TD]
[/TR]
[TR]
[TD]Havering 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Blueharts 1[/TD]
[/TR]
[TR]
[TD]Dereham 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Bedford 1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26-Sep-15[/TD]
[/TR]
[TR]
[TD]CoP 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Dereham 1[/TD]
[/TR]
[TR]
[TD]Chelmsford 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Old Loughts 1[/TD]
[/TR]
[TR]
[TD]Cambridge Uni 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Wapping 1[/TD]
[/TR]
[TR]
[TD]Cambridge City 2[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]St Albans 1[/TD]
[/TR]
[TR]
[TD]Blueharts 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Ipswich 1[/TD]
[/TR]
[TR]
[TD]Bedford 1[/TD]
[TD][/TD]
[TD]:[/TD]
[TD][/TD]
[TD]Havering 1[/TD]
[/TR]
</tbody>[/TABLE]

 

[123rickfear]

on the other tab are you searching for a fixture or a team's fixtures?

i.e on the other tab if you type in "Old Loughts 1 : CoP1" do you want it to return the date "19-Sep-15", or do you want to be able to type in a team name and return all the fixtures for that team?

 

[jbloom17]

Hi, thanks for trying to help..

im looking to type a team name and then it returns all fixtures for that team.

 

[123rickfear]

this is a tough one.

I can get the home games in one column, and the away games in another, but am still trying to get one list.

Let me know if this will do for now.

Rick

 

[jbloom17]

Hi Rick,

That could also work, thank you.

Takes away the manual filter, cut and paste.

Thank you so much for your help

 

[123rickfear]

in your original question is the home in team in column a and the away team in column c?

 

[jbloom17]

Yep, thanks

 

[123rickfear]

on the original sheet in D1 put =IF(ISNUMBER(A1),A1,"")

then in D2 put =IF(ISNUMBER(A2),A2,D1) and drag down.

in E2 put =TEXT(D2,"dd/mm/yyyy")&" - "&A2&" v "&C2 and drag down.

in sheet 2 in B1 put {=INDEX(Sheet1!$E$2:$E$50,SMALL(IF(Sheet2!$A$1=Sheet1!$A$2:$A$50,ROW(Sheet1!$A$2:$A$50)-ROW(Sheet1!$A$2)+1),ROW(1:1)))}

ensure you don't type copy the {}, you need to enter the formual then ctrl+shift+enter. then drag down, this should give you all your home games.

in C1 put {=INDEX(Sheet1!$E$2:$E$50,SMALL(IF(Sheet2!$A$1=Sheet1!$C$2:$C$50,ROW(Sheet1!$C$2:$C$50)-ROW(Sheet1!$C$2)+1),ROW(1:1)))}

again don't type/ copy the {}, then drag down.

you should now have a list of home games in column a and away games in column B.

Rick

 

[jbloom17]

Superstar Thank you