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

 

[Dram]

Sounds like you could get round that just using a pivot tabel to combine the two different divisions. As long as they are in the same format you could have a sheet hidden linking both tables and then do a pivot table on that sheet. Would give you the combined results.

There are other ways to do it as well but the pivot table is probably easiest.

Dram

 

[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

 

[romulus6]

Microsoft Excel has a handy tool to let you do just this without using pivot tables.

Please look at below Link and PM or mail me the sheet if you want further Help.

http://i29.photobucket.com/albums/c287/romulus6/driver-excel.jpg

In this example I have used the VLOOKUP Function. I created a list of drivers names firstly on sheet 3. Then using the Vlookup Function looked for driver B in sheet 1 and displayed his points in column H. I did the same for sheet 2 and displayed the points in column I. I then added both of these together (column J) using SUMIF formula.

To use VLOOKUP is easy if you follow microsoft`s "help on this function" link
which explains it in detail.
http://office.microsoft.com/en-gb/excel/HP052093351033.aspx

Hope this helps Dude if not get in touch