• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sorting golf members and trophies

G

guitarman

Member
Hi there

I am new on here and also pretty new to Excel.I have a problem.I was wondering if someone

Could help me out with this I am on a Golf committee and I have been given the task of finding

Out the solution to this.We have some 2-3hundred members and we play for 15 different trophies

A year and my task is to find out how many times each individual member has won each individual

Trophy over the past years.I have simplified it by making the formula numbers instead of names

I can alter that accordingly. The layout is something like this.

Members Trophies

A B C D E F

43 26 28 131 32 1

79 14 81 204 233 2

7 216 19 23 26 3

101 16 107 57 30 4

38 141 19 20 58 5


So I have to find how many times say member 43 has won trophy I,2,3,4,5 etc and the same with all the other members. Do I use a vlookup countif sum product macro or what I just don’t know where to start. Many Thanks in advance
 
From your layout, I'm not sure which column/row indicates names or trophy.


Assuming the source data is something like:

[pre]
Code: 
Name   Trophy  Year
Bob      A     2010
Sue      B     2011
Tom      A     2012
[/pre]
To get your totals you could either use a PivotTable to quickly get a list of everyone and their trophy counts, or you would use a SUMPRODUCT formula. With stated layout, it would be something like:

=SUMPRODUCT(--(NameRange="Bob"),--(TrophyRange="A"))
 
Hi ,


Can you check out the following worksheet ?


https://skydrive.live.com/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21164


Please note that the COUNTIFS function is not available in Excel 2003 ; you can replace a formula like this :


=COUNTIFS($A$2:$A$131,$G3,$C$2:$C$131,H$2)


with this :


=SUMPRODUCT(--($A$2:$A$131=$G3)*--($C$2:$C$131=H$2))


Narayan
 
Hi Luke M and NARAYANK991

It seems as though my layout has got somewhat messed up it should be like this

Members Trophies

8 26 28 31 82 1

13 4 8 20 33 2

7 16 19 83 126 3

1 61 20 17 30 4

3 14 94 20 76 5

5

So I need to know how many times members in columns A,B,C,D,E have won each

trophy in column F.i.e how many times member 17 has won trophies 1,2,3,4,5,etc upto

trophy 15.As with all the other members
 
Hi there

it seems that when I put in the layout it just all crams up together

but to try and give you the idea it is column

A B C D E

8 26 28 31 82 and column

F

1

I hope that comes out right
 
guitarman,

Place a tick mark before and after your table (the key to the left of number 1, aka tilde key) and the forum chat won't mess up your spacing.

[pre]
Code: 
this   is    with   tick   marks
[/pre]
 
Hi NARAYANK991

Yes I have tried that but to no avail i had figures of 0,38 and one of 274 I don't think that is right somehow. All I want really is some way I can find out how many times members

in columns A,B,C,D,E have won the various trophies in column F. I have 5 columns because

to list all members in one column would have an enormous column length anyway it is easier for me to have it that way and to have them as numbers rather than

Peter Andrew Jackson etc would take forever so it better to use their club number.
 
Hi Luke M

Well here goes with another effort lets see If I can get it right


Code: 
Members                   Trophies

[code]A  B  C  D  E                   F

[code]25 38 71 88 94                  1

[code]18 43 95 62 7                   2

1 14 57 44 184 3[/code]

41 55 32 77 15 4[/code]

10 66 81 66 98 5[/code]

well heres hoping that works.
 
Hi Luke

One more try


Code: 
Members                    [code]Trophies

[code]A  [code]B  [code]C  [code]D  [code]E          [code]F

[code]25 [code]38 [code]71 [code]88 [code]94         [code]1

[code]18 [code]43 [code]95 [code]62 [code]7         2

1[/code] 14[/code] 57[/code] 44[/code] 184[/code] 3[/code]

41[/code] 55[/code] 32[/code] 77[/code] 15[/code] 4[/code]

10[/code] 66[/code] 81[/code] 68[/code] 98[/code] 5[/code]

HOPE THAT DOES IT.
 
Hi

Last try at this


¬ Members¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬Trophies¬

¬ A¬ B¬ ¬C¬ ¬D¬ ¬E¬¬¬¬¬¬¬¬¬¬¬¬¬¬F¬

¬ 25 38¬71¬¬88¬¬94¬¬¬¬¬¬¬¬¬¬¬¬¬1¬

¬ 18¬43¬95¬¬62¬¬¬7¬¬¬¬¬¬¬¬¬¬¬¬¬2¬

¬¬1¬14¬57¬¬44¬¬184¬¬¬¬¬¬¬¬¬¬¬¬3¬

¬ 41¬55¬32¬¬77¬¬15¬¬¬¬¬¬¬¬¬¬¬¬¬4¬

¬ 10¬66¬81¬¬68¬¬98¬¬¬¬¬¬¬¬¬¬¬¬¬5¬

lets see if that works
 
So, members 25, 38, 71, 88, and 94 each won trophy #1?


If assuming, I'll assume your building a new table with all the club members listed as row labels, and the different trophies listed as column labels.

Formula would then be:

=SUMPRODUCT((DataRange=Member)*(TrophyRange=Trophy))


From the example, DataRange would be $A$2:$E$16, and TrophyRange would be $F$2:$F$6

If initial assumption was incorrect, please clarify.
 
Hi Luke


no members 25,38,71,88,94 are just examples. The idea is to find out how many times

they have won trophies 1,2,3,4,5 etc and the same with all the members. For example

member 25 could have won trophy 1 say 3 times and trophy 4 Twice and so on for all the others.
 
It doesn't sound like this is the data that will help us then. Is there a list somewhere then that says who's won what trophy? Would be very nice if this was a true data table that looked like:

[pre]
Code: 
Data  Member Trophy
Jan     23     A
Jan     56     B
Feb     42     C
etc.
[/pre]
Then we could quickly run a PivotTable to summarize everything. As it is now, I'm very confused, and not sure whether the example you're posting is supposed to be the desired output, or the data input.
 
A suggestion is to change how you put the data in. If you used a list containing rows with:


"Member Name", "Trophy Name", "Year"


There are a number of fairly simple ways (a pivot table would work) to generate the table you're looking for.
 
Hi Luke M


The data I am posting is the (Data Input) not the desired output. I am assuming

it would end up as an array formula. something like this.

-----1---2---3---4---5---6---7---8---9---10---11---12---13---14---15-Tophies

Mems

1----2---0---1---0---3---0---2---1--etc

2----1---1---0---2---1--etc

3----0---3---1---1---0--etc

4----1---1---0---0---1--etc

5----0---0---1---0---1--etc

6----2---1---0---1---0--etc

etc

I don't know if that is clear but that is what the final result should look like
 
Hi guitarman,


My earlier formula should work for you then.

=SUMPRODUCT((DataRange=Member)*(TrophyRange=Trophy))


Using some cell references, it might look like:

=SUMPRODUCT((Data_Sheet!$A$2:$E100=$A2)*(Data_Sheet!$F$2:$F$100=B$2))


Adjust range sizes as needed.
 
Hi Luke


Well I have tried that formula just now and all I get is zeros(0).I have another formula

which I made several months ago for finding pairs (how often 1 member played with another)

i.e.member 1 playing with member 25,40,50,60,etc and that worked fine but it does not

work on this problem I don't know why not this is the formula

=IF(ISNA(VLOOKUP(Pairs!$A2 & "." &Pairs!C$1,PairStats!$A:$D,4,FALSE)),"",VLOOKUP(Pairs!$A2 & "." &Pairs!C$1,PairStats!$A:$D,4,FALSE)).

any ideas?
 
Hi NARAYANKA 911


I have tried that formula you gave me to download and I have got it to do the first

row Member 1 and that appears to be right i have checked it manually. But it does not do the rest Members 2,3,4,5,etc etc it just enter snippets and the rest of them are complete blanks (0). Any Ideas
 
guitarman,


Do you have the absolute references (the dollar signs) in the correct spots in your formula? It sounds like the references are getting shifted when you copy down to the other members.
 
Hi Luke


Yes I have checked all the absolute references and they are the same in every row

But I have 5 columns A,B,C,D,E and column F is the trophies column the formula I have is

=COUNTIFS($A$2:$A$1250,$I3,$G$2:$G$1250,J$2) it wont allow me to go A$2:E$274. so I think it is not looking at the other columns what do you think?
 
Replace that COUNTIFS with SUMPRODUCT

=SUMPRODUCT(($A$2:$E1250=$I3)*($F$2:$F$1250=J$2))
 
HI Luke


changed that to the sumproduct formula it works on the first row Member 1 and through

the whole row of trophies upto 15. But all the rest of the members cells are filled with

#N/A.But when i was dragging them across and down I did notice it filled with all the

relevant numbers but when I released the mouse they all filled up with #N/A except the

first row which is perfect so we are not faraway. any ideas?
 
Hi ,


Your problem is still in the process of being resolved ; I think if you upload your worksheet , it would be resolved in just one hour.


Narayan
 
Hi NARAYANKA


I cannot upload the files because the Forums rules do not allow it apparently

and more to the point I would not know how to upload it because I have never

uploaded anything in my life so I would not have a clue where to begin

Mike
 
You must log in or register to reply here.
Back
Top