help with list of names and numbers

[mrjboyle]

Hi All, I'm back again

I have a list of 100 names in column C and corresponding points in column E

Some members in the list has many entries, example:- the member scored point many times.

How can I sort the list into one list where the formula looks at the list, totals all there point and then show the members name with total points i.e.

Mark McColl 120
Mark Brown 80
Jo Smith 160
and so on

Then it would be handy if it showed the top 20 names with total points

Thanks in advanced

Johnny

[Pepe Le Mokko]

Have you tried a Pivot Table ?

[mrjboyle]

No Don't know how

[Pepe Le Mokko]

You can start at http://www.contextures.com/CreatePivotTable.html

[mrjboyle]

does a pivot table outo update if the data is changed?

[alansidman]

There is a refresh button on the ribbon when you have the cursor set in the PT. I believe that there is a setting in the options to auto refresh.

http://office.microsoft.com/en-us/ex...102840043.aspx

[mrjboyle]

OK thanks for your help, but I need it to auto update, I wont be the person using this workbook

[alansidman]

Did you look at the link -- it shows auto update.

If that doesn't work for you, then you will need to use the Worksheet_Change Event in VBA

[mrjboyle]

Thanks again for your help, but you need to close and reopen the pivot table for it to refresh

[Tony Valko]

When you say "update", does that mean you'll be adding new data to the lists as time goes by?

We can use formulas to extract the unique names into a new list and get their totals.

Then, we can use the technique described here to get the top 20 list:

http://www.excelforum.com/excel-form...ml#post3452103

Want to try this method?

[mrjboyle]

Hi Tony

Yes the data will come in monthly and I will get the list of the top 20 winners and there points
these are put into 5 separate sheets Comp1, Comp2 and so on
I have got another sheet that copies the data into a list of 100 (5x20) names and points automatically
The problem is, any member can enter up to 3 images per month so they will show up on the list up to 3 times, times that by 5 months
I need to sort the members names and total the points and then show me the top 20 which will then show in the league table
The pivot table does this exactly as I want but does not auto update
I been thinking about INDEX/RANK but am struggling with the formula

Thanks in advance for any help you can give

Johnny

[Tony Valko]

Hi Tony

Yes the data will come in monthly and I will get the list of the top 20 winners and there points
these are put into 5 separate sheets Comp1, Comp2 and so on
I have got another sheet that copies the data into a list of 100 (5x20) names and points automatically

Hmmm...

Is that name list made up of 5 columns by 20 rows?

Where are the points entered? Is it something like this:



Data Range

	A	B	C	D	E	F
2	Name1	44	Name3	86	Name6	83
3	Name2	9	Name2	88	Name4	24
4	Name3	80	Name7	14	Name7	20
5	Name4	31	Name6	7	Name3	91
6	Name5	61	Name9	15	Name7	30
7	Name6	66	Name8	2	Name5	99

If so, that makes it a lot more complicated to do using formulas. It can still be done but the resultant formulas would be very long and super complicated.

You may be better off using a pivot table. Maybe you can add a button to the sheet and get someone to write you a macro to refresh the PT then you'd attach that macro to the button.

Whenever you want to refresh the PT you simply click the button.

I'm not a programmer so I can't help you with any of that!

[mrjboyle]

No I have works it so that Column C is populated with the names and column E their points
from cell 4 to 104

Cheers

Johnny

[Tony Valko]

The forum won't allow me to create a table to display the sample data. Says it's got too many characters! So, I'll have to post a sample file.

Ok, here's a smaller version of what you want to do.

mrjboyle.xlsx

Extract the unique names and sort them in ascending order...

Enter this array formula** in G4:

=IFERROR(INDEX(C$4:C$20,MATCH(0,COUNTIF(C$4:C$20,"<"&C$4:C$20)-SUM(COUNTIF(C$4:C$20,"="&G$3:G3)),0)),"")

Enter this formula in H4:

=IF(G4="","",SUMIF(C$4:C$20,G4,E$4:E$20))

Select G4:H4 and copy down to G20:H20

Enter the top N number in J4. I created a top 5 list in the sample file, in your case the top N number would be 20.

Enter this formula in K4. This will return the count of records that fall within the top N.

=COUNTIF(H4:H20,">="&LARGE(H4:H20,J4))

Enter this array formula** in L4:

=IF(ROWS(L$4:L4)>K$4,"",INDEX(G:G,SMALL(IF(H$4:H$20=M4,ROW(H$4:H$20)),COUNTIF(M$4:M4,M4))))

Enter this formula in M4:

=IF(ROWS(M$4:M4)>K$4,"",LARGE(H$4:H$20,ROWS(M$4:M4)))

Select L4:M4 and copy down to L20:M20.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[mrjboyle]

Just finished implementing this into my sheet, changing numbers to match and it work a treat
You are a genius

Many thanks

Johnny

[Tony Valko]

You're welcome. Thanks for the feedback!

[Tony Valko]

You are a genius

Spread the word!