+ Reply to Thread
Results 1 to 4 of 4

Help needed with addiing point values to a single line and then re-ordering.

  1. 12-07-2013, 04:12 AM #1
    Ohio Tournaments
    Ohio Tournaments is offline
    Registered User
    Join Date
    12-07-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    15

    Help needed with addiing point values to a single line and then re-ordering.

    Hello all-

    I have a question about how to make a formula, thanks in advance for your assistance.

    Ok, so my website hosts amateur wrestling tournaments and post results, what we are attempting to do is make a point system whereby wrestler will get so many points for each placement at a tournament, and then be "ranked" in order of how many points they have within their age group and weight class. So in other words, 20 wrestlers will be ranked in order of points at 11-12 75 pounds, another 20 wrestlers will be ranked at 11-12 80 pound weight class, and so forth. Altogether there are about 50 age group/weight class combinations, and probably 100 tournaments going into the ranking/point system, so as you can imagine, it is something that needs to be done automatically by excel. We get new results for in several tournaments every week, hoping to just "dump" in the new results for each tournament and have it automatically update the standings.

    So here's how far I've gotten. I list the results in a new spreadsheet, and it looks like this. The third column is weight class, the far right column is how many points the wrestler accumulated at that tournament. Note that I just listed results for the same tournament twice in a row, for way of example, because it will be easier to illustrate my problem.

    DUSTIN OTERO 5-6 Rookie 40 5
    TEEGAN GILMORE 5-6 Rookie 40 3
    BEAU WILKOVICH 5-6 Rookie 40 2

    HAYDEN SIGLER 5-6 Rookie 45 8
    BLAKE BARTOS 5-6 Rookie 45 6
    GREY BURNETT 5-6 Rookie 45 4
    CALEB NICELY 5-6 Rookie 45 3
    BEAU FIERCE 5-6 Rookie 45 2
    HUNTER BENHAM 5-6 Rookie 45 1

    CONNOR HANEY 5-6 Rookie 50 8
    EMMANUEL CAIN 5-6 Rookie 50 6
    NICHOLAS PARKS 5-6 Rookie 50 4
    JEFF MASLAR 5-6 Rookie 50 3
    DIEGO CASTRO 5-6 Rookie 50 2

    E.J. GERMAN 5-6 Rookie 55 5
    RAYMOND FIERCE 5-6 Rookie 55 3
    AIDEN SUPERS 5-6 Rookie 55 2
    ALYSSA RODKEY 5-6 Rookie 55 1

    GAVYN MCCRAY 5-6 Rookie 60 5
    ARMAN GABLE 5-6 Rookie 60 3
    ANTHONY SEVIEK 5-6 Rookie 60 2
    AGE:

    JOEL RAY 7-8 Rookie 45 4
    R.J. SYCZ 7-8 Rookie 45 2

    DOMINIC CASTRO 7-8 Rookie 50-55 8
    CONNOR HOLM 7-8 Rookie 50-55 6
    SHANE HICKMAN 7-8 Rookie 50-55 4
    DOMONIC SPRURIG 7-8 Rookie 50-55 3
    ISAIAH CRIBE 7-8 Rookie 50-55 2

    KINGSTON URIAS 7-8 Rookie 60 5
    MARK WISSEL 7-8 Rookie 60 3
    BRENDEN WATCHHAM 7-8 Rookie 60 1

    JESSE FARMER 7-8 Rookie 66 8
    KYLE SANBORN 7-8 Rookie 66 6
    CAYDEN FIALA 7-8 Rookie 66 4
    AVERY VIANCOURT 7-8 Rookie 66 3
    AYDEN DUNN 7-8 Rookie 66 2
    LANDON KINAS 7-8 Rookie 66

    CARLOS JOHNSON 7-8 Rookie 68-72 5
    AIDEN MAYS 7-8 Rookie 68-72 3
    MJ SEBRING 7-8 Rookie 68-72 2
    DREW WILSON 7-8 Rookie 68-72 1

    ANDREW SUPERS 7-8 Rookie 85 5
    GABE TITMAS 7-8 Rookie 85 3
    ANDREW HENDERSON 7-8 Rookie 85 2

    ANDREW FLESHER 7-8 Rookie 85-91 4
    ANDREW HENDERSON 7-8 Rookie 85-91 2
    DUSTIN OTERO 5-6 Rookie 40 5
    TEEGAN GILMORE 5-6 Rookie 40 3
    BEAU WILKOVICH 5-6 Rookie 40 2

    HAYDEN SIGLER 5-6 Rookie 45 8
    BLAKE BARTOS 5-6 Rookie 45 6
    GREY BURNETT 5-6 Rookie 45 4
    CALEB NICELY 5-6 Rookie 45 3
    BEAU FIERCE 5-6 Rookie 45 2
    HUNTER BENHAM 5-6 Rookie 45 1

    CONNOR HANEY 5-6 Rookie 50 8
    EMMANUEL CAIN 5-6 Rookie 50 6
    NICHOLAS PARKS 5-6 Rookie 50 4
    JEFF MASLAR 5-6 Rookie 50 3
    DIEGO CASTRO 5-6 Rookie 50 2

    E.J. GERMAN 5-6 Rookie 55 5
    RAYMOND FIERCE 5-6 Rookie 55 3
    AIDEN SUPERS 5-6 Rookie 55 2
    ALYSSA RODKEY 5-6 Rookie 55 1

    GAVYN MCCRAY 5-6 Rookie 60 5
    ARMAN GABLE 5-6 Rookie 60 3
    ANTHONY SEVIEK 5-6 Rookie 60 2
    AGE:

    JOEL RAY 7-8 Rookie 45 4
    R.J. SYCZ 7-8 Rookie 45 2

    DOMINIC CASTRO 7-8 Rookie 50-55 8
    CONNOR HOLM 7-8 Rookie 50-55 6
    SHANE HICKMAN 7-8 Rookie 50-55 4
    DOMONIC SPRURIG 7-8 Rookie 50-55 3
    ISAIAH CRIBE 7-8 Rookie 50-55 2

    KINGSTON URIAS 7-8 Rookie 60 5
    MARK WISSEL 7-8 Rookie 60 3
    BRENDEN WATCHHAM 7-8 Rookie 60 1

    JESSE FARMER 7-8 Rookie 66 8
    KYLE SANBORN 7-8 Rookie 66 6
    CAYDEN FIALA 7-8 Rookie 66 4
    AVERY VIANCOURT 7-8 Rookie 66 3
    AYDEN DUNN 7-8 Rookie 66 2
    LANDON KINAS 7-8 Rookie 66

    CARLOS JOHNSON 7-8 Rookie 68-72 5
    AIDEN MAYS 7-8 Rookie 68-72 3
    MJ SEBRING 7-8 Rookie 68-72 2
    DREW WILSON 7-8 Rookie 68-72 1

    ANDREW SUPERS 7-8 Rookie 85 5
    GABE TITMAS 7-8 Rookie 85 3
    ANDREW HENDERSON 7-8 Rookie 85 2

    ANDREW FLESHER 7-8 Rookie 85-91 4
    ANDREW HENDERSON 7-8 Rookie 85-91 2


    Then, I sort them by Age group and weight class, and rank them in order of points within that age group and weight class, here's how it looks (obviously it looks much more organized on Excel):

    DUSTIN OTERO 5-6 Rookie 40 5
    DUSTIN OTERO 5-6 Rookie 40 5
    TEEGAN GILMORE 5-6 Rookie 40 3
    TEEGAN GILMORE 5-6 Rookie 40 3
    BEAU WILKOVICH 5-6 Rookie 40 2
    BEAU WILKOVICH 5-6 Rookie 40 2
    HAYDEN SIGLER 5-6 Rookie 45 8
    HAYDEN SIGLER 5-6 Rookie 45 8
    BLAKE BARTOS 5-6 Rookie 45 6
    BLAKE BARTOS 5-6 Rookie 45 6
    GREY BURNETT 5-6 Rookie 45 4
    GREY BURNETT 5-6 Rookie 45 4
    CALEB NICELY 5-6 Rookie 45 3
    CALEB NICELY 5-6 Rookie 45 3
    BEAU FIERCE 5-6 Rookie 45 2
    BEAU FIERCE 5-6 Rookie 45 2
    HUNTER BENHAM 5-6 Rookie 45 1
    HUNTER BENHAM 5-6 Rookie 45 1
    CONNOR HANEY 5-6 Rookie 50 8
    CONNOR HANEY 5-6 Rookie 50 8
    EMMANUEL CAIN 5-6 Rookie 50 6
    EMMANUEL CAIN 5-6 Rookie 50 6
    NICHOLAS PARKS 5-6 Rookie 50 4
    NICHOLAS PARKS 5-6 Rookie 50 4
    JEFF MASLAR 5-6 Rookie 50 3
    JEFF MASLAR 5-6 Rookie 50 3
    DIEGO CASTRO 5-6 Rookie 50 2
    DIEGO CASTRO 5-6 Rookie 50 2
    E.J. GERMAN 5-6 Rookie 55 5
    E.J. GERMAN 5-6 Rookie 55 5
    RAYMOND FIERCE 5-6 Rookie 55 3
    RAYMOND FIERCE 5-6 Rookie 55 3
    AIDEN SUPERS 5-6 Rookie 55 2
    AIDEN SUPERS 5-6 Rookie 55 2
    ALYSSA RODKEY 5-6 Rookie 55 1
    ALYSSA RODKEY 5-6 Rookie 55 1
    GAVYN MCCRAY 5-6 Rookie 60 5
    GAVYN MCCRAY 5-6 Rookie 60 5
    ARMAN GABLE 5-6 Rookie 60 3
    ARMAN GABLE 5-6 Rookie 60 3
    ANTHONY SEVIEK 5-6 Rookie 60 2
    ANTHONY SEVIEK 5-6 Rookie 60 2
    JOEL RAY 7-8 Rookie 45 4
    JOEL RAY 7-8 Rookie 45 4
    R.J. SYCZ 7-8 Rookie 45 2
    R.J. SYCZ 7-8 Rookie 45 2
    KINGSTON URIAS 7-8 Rookie 60 5
    KINGSTON URIAS 7-8 Rookie 60 5
    MARK WISSEL 7-8 Rookie 60 3
    MARK WISSEL 7-8 Rookie 60 3
    BRENDEN WATCHHAM 7-8 Rookie 60 1
    BRENDEN WATCHHAM 7-8 Rookie 60 1
    JESSE FARMER 7-8 Rookie 66 8
    JESSE FARMER 7-8 Rookie 66 8
    KYLE SANBORN 7-8 Rookie 66 6
    KYLE SANBORN 7-8 Rookie 66 6
    CAYDEN FIALA 7-8 Rookie 66 4
    CAYDEN FIALA 7-8 Rookie 66 4
    AVERY VIANCOURT 7-8 Rookie 66 3
    AVERY VIANCOURT 7-8 Rookie 66 3
    AYDEN DUNN 7-8 Rookie 66 2
    AYDEN DUNN 7-8 Rookie 66 2
    LANDON KINAS 7-8 Rookie 66
    LANDON KINAS 7-8 Rookie 66
    ANDREW SUPERS 7-8 Rookie 85 5
    ANDREW SUPERS 7-8 Rookie 85 5
    GABE TITMAS 7-8 Rookie 85 3
    GABE TITMAS 7-8 Rookie 85 3
    ANDREW HENDERSON 7-8 Rookie 85 2
    ANDREW HENDERSON 7-8 Rookie 85 2
    DOMINIC CASTRO 7-8 Rookie 50-55 8
    DOMINIC CASTRO 7-8 Rookie 50-55 8
    CONNOR HOLM 7-8 Rookie 50-55 6
    CONNOR HOLM 7-8 Rookie 50-55 6
    SHANE HICKMAN 7-8 Rookie 50-55 4
    SHANE HICKMAN 7-8 Rookie 50-55 4
    DOMONIC SPRURIG 7-8 Rookie 50-55 3
    DOMONIC SPRURIG 7-8 Rookie 50-55 3
    ISAIAH CRIBE 7-8 Rookie 50-55 2
    ISAIAH CRIBE 7-8 Rookie 50-55 2
    CARLOS JOHNSON 7-8 Rookie 68-72 5
    CARLOS JOHNSON 7-8 Rookie 68-72 5
    AIDEN MAYS 7-8 Rookie 68-72 3
    AIDEN MAYS 7-8 Rookie 68-72 3
    MJ SEBRING 7-8 Rookie 68-72 2
    MJ SEBRING 7-8 Rookie 68-72 2
    DREW WILSON 7-8 Rookie 68-72 1
    DREW WILSON 7-8 Rookie 68-72 1
    ANDREW FLESHER 7-8 Rookie 85-91 4
    ANDREW FLESHER 7-8 Rookie 85-91 4
    ANDREW HENDERSON 7-8 Rookie 85-91 2
    ANDREW HENDERSON 7-8 Rookie 85-91 2

    So here's my problem. Once I sort, I need the system to automatically add each wrestlers points together that he has received at each tournament, list that wrestler only once, and rank/order them by total points, instead of listing them multiple times for the individual points they list at each tournament (as you see above).

    Once again, thanks in advance, the extent of my knowledge currently ends at basic sorts and sum functions for columns, so any insight is much appreciated.
    Reply With QuoteRegister To Reply
  2. 12-07-2013, 04:30 AM #2
    MarvinP
    MarvinP is offline
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Help needed with addiing point values to a single line and then re-ordering.

    Hi,
    This is an easy pivot table answer, if any pivot tables are easy. See the attached where I took your data and text to columns. Then did a pivot table and grouped it and summed the pts. Hope this helps.

    As you add data to the bottom of the rows you will need to have the pivot table span the new data. Also read about refreshing pivot tables...
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.
    Reply With QuoteRegister To Reply
  3. 12-07-2013, 05:06 AM #3
    Ohio Tournaments
    Ohio Tournaments is offline
    Registered User
    Join Date
    12-07-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2013
    Posts
    15

    Re: Help needed with addiing point values to a single line and then re-ordering.

    Marvin:

    Amazingly helpful! I will read up on pivot tables to figure out exactly how to do this (although, what you have is about 80% of the way there lol). Thanks again!
    Reply With QuoteRegister To Reply
  4. 12-10-2013, 02:20 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Help needed with addiing point values to a single line and then re-ordering.

    Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

    In future, to mark your thread as Solved, you can do the following -
    Select Thread Tools-> Mark thread as Solved.

    Incase your issue is not solved, you can undo it as follows -
    Select Thread Tools-> Mark thread as Unsolved.

    Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Single trend line for different y values and common x value
    By vikrannt.patil20 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-17-2013, 03:29 AM
  2. Straight Vertical and Horizontal line through a single scatter point.
    By jhall488 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-10-2012, 10:06 AM
  3. [SOLVED] Return Multiple Values based on a single reference point
    By qfalker in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-20-2012, 01:00 PM
  4. Create a pareto chart with % line starting from top point of first data point
    By jeffreybrown in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-27-2011, 10:47 PM
  5. [SOLVED] How to extend a single point into a line in a chart?
    By sibimelvin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 06-22-2005, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1