Excel problem - joining/merging results from 2 separate sheets into a 3rd

[Neill_J]

Hi

Am stuck on an excel problem - cant see a way to do this - wondered if anyone can think of a way to accomplish it without resorting to manual entry or manipulation?

Problem:

I am maintaining a spreadsheet which is used to calculate points/positions in an online rFactor racing league
Currently there are 2 individual championships - Formula 1 & Formula 3, with the results from the 2 of these then merged into a combined one (champion of champions)

The individual championships are fine, but when I try to combine them I run into the problem - there are some names in both sheets, but others only have 1
(i.e. driver A only races in F1 whereas Driver B is in F1 & F3 and Driver C only races F3 etc)

Simplified example below:

Sheet 1 (F1 results/standings)

Driver Name - Points
Driver A - 20
Driver B - 10
Driver D - 5

Sheet 2 (F3 results/standings)

Driver Name - Points
Driver B - 15
Driver C - 19
Driver D - 10

Sheet 3 (Combined Results - sorted by points)

Driver Name - Points
Driver B - 25
Driver A - 20
Driver C - 19
Driver D - 15

Is there some way to read the names & points from Sheet 1 (F1 results) and Sheet 2 (F3 results) and write the names and points into Sheet 3 (Combined Results) which checks for duplicates names and where it finds them joins them (and the points for that name) together? And also, where it finds names only on either Sheet 1 or Sheet 2 also copies them (and the points) to the Combined Sheet (as in the example above)

Hope I have got the concept across?
I can email the excel sheet if required so you can see clearer what I mean - I don’t have access to my ftp at the moment to post it unfortunately

Thanks - Neill

 

[Neill_J]

Hi

Thanks for the tip on where to look.
I have had a read up now on this and had a go at using pivot tables but have concluded that I don't know what on earth I am doing

Here is a screen of what I tried:

http://img236.imageshack.us/my.php?image=image1iu9.jpg

Basically this shows the example in my first post with source data for F1 & F3 (upper left - A2:B6 & D2:E6)
I have also included at the bottom a manual calculation of what I expected to get out from this (combined) - this is my goal for when its automated (D17:E22)

I then set about trying to use a pivot table, here is what I did....(excel 2003 btw)

Data > pivot table and pivot chart report > step 1 of 3 > selected multiple consolidation ranges > next > step 2a of 3 > left at default (single page) > step 2b of 3 > on range I added the 2 sources (A4:B6) add, then (D4:E6) add > next > step 3 of 3 > clicked finish

I then get the data you see on the top right (its not quite right), am sure its something I am doing wrong but have now spent an hour or more on it and have run out of ideas

I have tried playing around with the layout options in step 3 of 3 menu but confess to not having a clue here what's being done...
any more tips/pointers much appreciated

BTW - do pivot tables always show the grey boxes at the top (such as Page1, all, sum of value, column etc) I was hoping to just be able to display output similar to the combined example if possible? (D17:E22)

Thanks - Neill