Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Results and League tables

I have set up a set of fixtures for a Skittle League.

I will enter in the results into the table (see pic)

I want to generate a league table but I need the following formula to work

User uploaded file

I want to lookup 2 columns which match a certain criteria


lookup a home team name [Ball Boys] and a away team name [Six Amigos] and return their pins result - either [PH] (pins hit at home) or [PA] (pins hit away)

so I can record them in the below table

User uploaded file

or if theres a better way of doing …


Regards


Neil

MacBook, Mac OS X (10.5.7), www.GetDex.co.uk

Posted on Mar 19, 2014 3:16 AM

Reply
Question marked as Top-ranking reply

Posted on Mar 19, 2014 12:37 PM

I would add a new column (which you can hide) to create a combined team name identifier. select column A and insert a new column before by using the key combaination:

<command> + <option> + <left arrow>


A2=C2&"_v_"&H2

select A2 and fill down as needed.


For the bottom table ( I am assuming the name of the top table is "Games"... adjust this name in my formulas to match your table name OR change your table name to match):


I would unmerge the columns and use a single column for a team. Like this:


User uploaded file


C2=IF(C$1=$B2, "EMPTY",IFERROR("<"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 5, 0)&"< "&"^"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 6, 0)&"^", "--"))


select C2, copy,

select C2 thru the end (in my example H7), paste


Now seelc the menu item "Format > Conditional Formatting" and set up like this:

User uploaded file

6 replies
Question marked as Top-ranking reply

Mar 19, 2014 12:37 PM in response to liquorpokerskittles

I would add a new column (which you can hide) to create a combined team name identifier. select column A and insert a new column before by using the key combaination:

<command> + <option> + <left arrow>


A2=C2&"_v_"&H2

select A2 and fill down as needed.


For the bottom table ( I am assuming the name of the top table is "Games"... adjust this name in my formulas to match your table name OR change your table name to match):


I would unmerge the columns and use a single column for a team. Like this:


User uploaded file


C2=IF(C$1=$B2, "EMPTY",IFERROR("<"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 5, 0)&"< "&"^"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 6, 0)&"^", "--"))


select C2, copy,

select C2 thru the end (in my example H7), paste


Now seelc the menu item "Format > Conditional Formatting" and set up like this:

User uploaded file

Mar 19, 2014 1:41 PM in response to Wayne Contello

I meant to mention...


I placed braces around the scores to direct you whose was whose...


e.g.:


Wayne

Wayne

EMPTY

Sam

--

Harvey

--

Rachel

--

John

<1< ^2^


<1< ^2^

is indicating that the 1 belongs to John

and 2 belongs to Wayne




This actually identifies a typo I have.... those should be reversed:

use this formula instead

=IF(C$1=$B2, "EMPTY",IFERROR("^"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 5, 0)&"^ "&"<"&VLOOKUP(C$1&"_vs_"&$B2, Games::$A:$F, 6, 0)&"<", "--"))



This is what it should look like:



Wayne

Sam

Harvey

Rachel

John

Tim


Wayne

EMPTY

--

--

--

--

--


Sam

--

EMPTY

--

^7^ <8<

--

--


Harvey

--

--

EMPTY

--

--

--


Rachel

--

--

^5^ <6<

EMPTY

--

--


John

^1^ <2<

--

--

--

EMPTY

--


Tim

--

^3^ <4<

--

--

--

EMPTY

Mar 20, 2014 2:35 AM in response to Wayne Contello

Hi Wayne,


I think I get it but still struggling to implement it.


Attached is a stripped down .Numbers doc would you be able to add a few in and then I can see and carry on.


On the Results table the PH (Pins hit by home Team) and PA (Pins hit by Away team) are the 2 columns I will manualy enter their scores.


Hope you can Help.

I am using Numbers 09 v2.3 but if you are higher I can look over it in iCloud


Neil

http://www.getdex.co.uk/numbers/Results.numbers.zip

Results and League tables

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.