League table spreadsheet help

**nrgroom** · 06-24-2011, 08:13 AM

Hi

I run a pretty basic spreasheet for our football team.

It's basically keeps a record of who is on the winning/losing/drawing team each game, so there is a row for each player like:

Neil W D W D L L

etc.

What I would like to do is have some analysis of how often two players are on the same team, ie they will have the same character (W,D or L) in the same column for that game.

Can anybody suggest a way to get excel to do this for me, to save me going through the list and doing it by hand?

Thanks

Neil.

Edited to attach spreadsheet - format of what I want is on sheet1 tab.

**brokenbiscuits** · 06-24-2011, 08:33 AM

Just an initial thought, a player with a D could be playing on either team?

**nrgroom** · 06-24-2011, 08:37 AM

Originally Posted by brokenbiscuits

Just an initial thought, a player with a D could be playing on either team?

Hi - yeah - i've got round that by using lower and upper case Ds.

Thanks

Neil.

**MarvinP** · 06-24-2011, 09:15 AM

Hi nrgroom and welcome to the forum,

Find the attached with a possible answer to your problem. I decided it would be best to have a table of all possible player names and then simply count how many times they had the same letter for each game. You can change the W, L or D letter to see how many matches. Sorry the formula isn't short or pretty but I think it does what you want.

My attachment created before OP updated his original post and put an attachment on it.

**brokenbiscuits** · 06-24-2011, 09:42 AM

I was thinking something along the lines of the attached in terms of seeing a record between 2 players?

As for the lower case/upper case D, excel doesn't recognise the difference, so I've used D and X.

All the stuff in yellow would probably be best hidden.

**brokenbiscuits** · 06-24-2011, 09:53 AM

Ah, I realised you had an attachment then got really confused when I opened it, but I've just re-read your post and realised you just want the total number of games players play together.
Back to the drawing board!

So you basically just want Sheet1 populated?

**brokenbiscuits** · 06-24-2011, 10:14 AM

You could expand on the formula in MarvinP's book - it makes it even more unwieldly to look at but it would give you the results you need.

See attached - again I've changed the ds to Xs.

**nrgroom** · 06-24-2011, 10:15 AM

Originally Posted by brokenbiscuits

Ah, I realised you had an attachment then got really confused when I opened it, but I've just re-read your post and realised you just want the total number of games players play together.
Back to the drawing board!

So you basically just want Sheet1 populated?

Yeah - I would prefer it in that format, really.

Many thanks for the suggestions so far.

**nrgroom** · 06-24-2011, 10:21 AM

Originally Posted by brokenbiscuits

You could expand on the formula in MarvinP's book - it makes it even more unwieldly to look at but it would give you the results you need.

See attached - again I've changed the ds to Xs.

That's great - exactly what i'm after.

Many thanks to all that helped.