Spreadsheet Confusion

Chris,

What do you want to see? Can you post the table you have now? You can selec tthe table, then paste here.

I think this may be a good start:

The table on the left is titled "Games" and is where you enter the players (columns A, B, E, and F), the scores (columns H and I). Column C, G, and J may be hidden and are caluclation columns used in other tables.

For the table Games:

C2=IF(A2>B2, B2&A2, A2&B2)

This is shorthand for in cell C2 type (or copy and paste from here) the formula:

"=IF(A2>B2, B2&A2, A2&B2)" without the first and last double quotes

select C2, copy

select column C, now hold the command key and click cell C1 (to unselect), paste

select column C, copy

select column F, paste

J2=IF(OR(ISBLANK(H2),ISBLANK(I2)),"", "Team "&IF(H2>I2, "1", "2"))

select J2, copy

select column J, now hold the command key and click cell J1 (to unselect), paste

enter your games as needed in this table.

Now create a table like show in the top, right called "Players".

In the ID column (column A) enter the player ID (consistent with what was used in the "Games" table)

In the Name column (column B) enter the player name

C2=COUNTIFS(Games::A, A2, Games::J, "Team 1")+COUNTIFS(Games::B, A2, Games::J, "Team 1")+COUNTIFS(Games::E, A2, Games::J, "Team 2")+COUNTIFS(Games::F, A2, Games::J, "Team 2")

D2=SUMIF(Games::A, A2, Games::H)+SUMIF(Games::B, A2, Games::H)+SUMIF(Games::E, A2, Games::I)+SUMIF(Games::F, A2, Games::I)

select C2 and D2 and fill down

Now create another table called "Teams" as shown in the bottom right. enter the player combinations as shown:

B2=VLOOKUP(LEFT(A2, 1),Players::A:B, 2, 0)&" and "&VLOOKUP(RIGHT(A2, 1),Players::A:B, 2, 0)

C2=COUNTIFS(Games::C, A2, Games::J, "Team 1")+COUNTIFS(Games::G, A2, Games::J, "Team 2")

select B2 and C2 then fill down

I am repeatdly using the same formula. so review:

countif() and its' related function countifs()

sumif()

The function reference is accesible from the web if you use the menu item "Held > Formulas and Function Help". Here is the same linke:

http://help.apple.com/functions/mac/4.0/

Secret,

The example I provided assumed (and you should do it this way too) that the players were designated in the top right table and given IDs as shown. I can provide a modification to hide the IDs and present real names. You should use the IDs as I showed in my example.

Please implement the previous step first then insert two columns after column F and then two columns after column B.

C2=VLOOKUP(A2,Players::$A:$B, 2, 0)

select C2, fill to the right one cell

select C2 and C3 and fill down as needed:

select C2 and C3, copy

select I2 thru J2, paste

select I2 thru J2, and fill down as needed:

now you can hide columns as needed. If you need to show the NON-playing person we can also make that change.