Excel Combining Multiple Rows

Question

I feel like I am missing something simple I would like to do with Excel but I am asking the question incorrectly on Google...here it goes.

I'm taking a look at some Excel sheets for a friend who runs a race timing company. At the end of a race he has an excel sheet with the following format for a series of races

Name | Gender | Age | Race 1 | Race 2 | Race 3
Bob    |    M       | 20   |      1      |             |
Al       |    M       | 24   |      2      |             |
Bob    |    M       | 20   |              |      2     |
Al       |    M       | 24   |              |      1     |

::Assume we don't care about time right now, just place::

I would like to do "something" (again I'm not sure what the proper term is, merge in Excel actually merges two adjecent cells together), where I can get the final output such that

Name | Gender | Age | Race 1 | Race 2 | Race 3
Bob    |      M     |   20 |      1      |      2     |
Al       |      M     |   24 |      2      |      1     |

I'm not sure how to collapse the data for the like rows together.

I'm not opposed to writing a little VBA, but I am thinking this is a built in Excel function but I'm not sure what it is called or how to make it "dance".

Thanks!

Answer 1

PivotTable.

Answer 2

The data format is making life a bit more difficult than it needs to be. Rather than having individual columns for race #1, race #2, race #3 etc, it would make life easier to have a column called "Race Number" and arrange the data like this:

Name | Gender | Age | Race Number | Place
Bob  | M      |  20 |           1 |     1
Al   | M      |  24 |           1 |     2
Bob  | M      |  20 |           2 |     2
Al   | M      |  24 |           2 |     1

This would make things like PivotTable (as suggested by Jason) a lot easier to work with