How to merge data into single output

Question

Okay here is what I have. How would I get the three identical wishbone plays to merge and sum their data?

Yes the data is already sorted by formation. I don't mind inputing it each time as I will probably add to the list several additional plays and formations. I just want to merge the times called together and the yards gained together. So the FSU RB1 OT should only be one cell and say 5 for times called and 18 for yards gained. I can do math in my head mostly to figure out the average. Sorry for not being specific enough.

Answer 1

A nice solution for this is to use a pivot table. A pivot table allows you to summarize data from your table. It can be used to keep a running total for each play type and calculate the average yards per play. With a pivot table, your data entry can be simple -- just add the new data to the end of the table and let the pivot table update the totals for you.

To set up your pivot table do the following:

1. Select your entire table, including headers. Go to the Home ribbon and select Format as Table and choose any visual format for your data entry table. The reason for doing this is that it assigns a name to the table (probably Table1 by default), and the name will refer to the entire table even if you add new data. You can find the name of the table by going to the Table Tools Design ribbon and looking under Properties.

   

2. Next, select the entire table, and go to the Insert ribbon and click Pivot Table. A dialog will appear asking you which source data to use and where to place the pivot table. Make sure the source is set to the name of your table, e.g., Table1. I would advise placing the pivot table on a new sheet, but you may put it on the same sheet if you like -- just make sure it's off to the side out of the way from your raw data. After choosing your output location, click OK.

3. You will be presented with something like this:

   

   You'll use the Field List on the right to set up your pivot table. For your particular needs, you'll want to drag and drop Play from the field list into the section labeled Row Labels. Also, drag and drop Times Called and Yards Gained into the Values section. By default these will show up as Sum of Times Called and Sum of Yards Gained, which is exactly what you want. This will result in the table you're trying to achieve.

   

Any time you've added new data and want to update the summary, just go to the pivot table, right-click anywhere inside the table and choose Refresh.

If you'd like to include the average yards per play in the pivot table, you will need to create a calculated field. To do this click somewhere inside the pivot table and go to the PivotTable Tools Options ribbon and click Formulas > Calculate Field....

Here you'll create a calculated field, called something like YPP. Define the field as

= 'Yards gained'/ 'Times called'

Click Add and then click OK. Your pivot table will now include a Sum of YPP field. This should display the average yards per carry for each play.