Combining Different Spreadsheets

[mlevy]

Hi,
I'm trying to combine several reports into one. They all have lists o
names with statistics associated with them. The problem is that the
don't all have the same names. Here's an example of what I mean:

List 1 - study 1
John 1.2
Mary 4.0
Ed 1.5
Tom 5.5

List 2 - study 2
Edna 4.5
John 2.5
Tom 5.5
Alicia 2.2

Goal......Study 1.................Study2
Alicia .................................2.2
John .....1.2 ......................2.5
Edna ................................4.5
Ed.........1.5
mary......4.0
Tom ......5.5......................5.5



So these list 1 and 2 both have some of the same names but also som
different names. How do I combine them to make the goal list with th
different results side by side? It seems like there should be some wa
to sort them against a master list of names so that there are blan
spaces for for people not on the individual lists

 

[thunt]

Try using the VLOOKUP command. You have a master list, will all the
names on it. Then use the VLOOKUP command to compare the name on the
master list againts the name/results on Lists 1 & 2. Below is an
example, bringing in the results from column 2 of sheet 2 where the
name matches cell A2 on sheet 1.

=VLOOKUP(A2,Sheet2!A:B,2,FALSE)

 

[Ken Wright]

Rearranging your data slightly - Using Data / Pivot Table & Chart Report :-

STUDY NAME SCORE
Study 1 - John 1.2
Study 1 - Mary 4.0
Study 1 - Ed 1.5
Study 1 - Tom 5.5
Study 2 - Edna 4.5
Study 2 - John 2.5
Study 2 - Tom 5.5
Study 2 - Alicia 2.2

Drag Name into Row fields, Study into Column fields and Score into Data
field and you will get exactly what you want.

Intro to Pivot tables

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------