Searching multiple cells in a column, splitting on a " vs " string and doing a bit of Math

Hi all,

Quiet day at the office so I am playing with excel trying to create a spreadsheet for a game I play every year with a few friends. Previously the spreadsheet has been largely manual but I decided to automate as much as possible.

Here's my dilemma. (See attachment)

Sheet 1 contains a list of fixtures and results.

Sheet 2 contains the league table.

So I need to search for a team in sheet A, their relevant result and award the points as necessary, 3 for a win, 1 for a draw, 0 for a loss.

I've tried LEFT, FIND and many other commands to split on the " vs " but have had no joy, I am wondering if I'm into the realms of VBA here?

Thanks

Dave

Is the attached the sort of thing you're after?

Wow that's perfect thanks, I need to take that apart now so it gets lodged in my memory but that's brilliant.

I also want to make the table automatically to show who's at the top but I'll try and work some logic on that before I bother this community

Thanks

Dave

Sorry back again; I've started to crunch the league table slightly and expand the search. However, when I have any cells with blank data in the Fixture column I am getting #value message.

Is there a way to say if a cell is blank then ignore it?

i.e.

...A Fixtures
1 13/12/2012
2 Manchester United
3 Liverpool
4 Arsenal
5
6 17/12/2012
7 Arsenal
8 Liverpool
9 Manchester United
10 Date (Next fixtures, etc)

Thanks

Dave

Is this in relation to the formula I wrote for you, or a new formula you're adding?

Hi Andrew,

A bit of both really. Expanding your formulas from A4:A7 to :A999 I get the #VALUE message where blank fields are involved (on the league table tab). I am wondering if it may be easier for me to start writing some VB around this as the formulas are getting monstrous

Thanks

Dave

Yup, they're pretty hideous formula.

The #VALUE error is because my formula assumes that the scores will always contain a "-", which delimited the home team's score from the away team's score. If that isn't there then things go a bit haywire.

You can try changing the formula in D3 to:

=SUMPRODUCT(--(ISNUMBER(FIND(B3 & " vs",Predictions!$A$4:$A$7))),--(VALUE(LEFT(Predictions!$B$4:$B$7,IFERROR(FIND("-",Predictions!$B$4:$B$7)-1,0)))>VALUE(MID(Predictions!$B$4:$B$7,IFERROR(FIND("-",Predictions!$B$4:$B$7)+1,0),255))))+ SUMPRODUCT(--(ISNUMBER(FIND("vs " & B3,Predictions!$A$4:$A$7))),--(VALUE(LEFT(Predictions!$B$4:$B$7,IFERROR(FIND("-",Predictions!$B$4:$B$7)-1,0)))<VALUE(MID(Predictions!$B$4:$B$7,IFERROR(FIND("-",Predictions!$B$4:$B$7)+1,0),255))))

But this is an array formula and must be entered using Ctrl-Shift-Enter, not just Enter.

Obviously the formula in columns E & F would require similar modification.

I think it's probably time for some VB as well. This might be a useful start:

Please Login or Register  to view this content.