how to compare multiple lists of data in excel

[Guest]

How to compare two or more lists of data?
For example: Comparing stocks held by different mutual funds
I want to identify the stocks which are common in different mutual funds
(MFs) i.e. like
MF X holds
ABC Co. - 500 units
DEF Co. - 300
MOP Co - 600
AAA Co.- 200

MF Y holds
CBA Co. - 500 units
MOP Co. - 900
FED Co - 600
BBB Co.- 200

MF Z holds
ABC Co. - 500 units
NPL Co. - 300
MOP Co - 100
CCC Co.- 200

Like this there are more than 20 MFs holding more than 35 stocks each.

I want to know the common stocks and their units in each for my study

RAJEEV MINOCHA

 

[Dave Peterson]

I'd lay out my data in 3 columns:

MFName StockName Units

Then use Data|pivottable to get that summary.

Select the whole range
Data|Pivottable
follow the wizard until you get to the step with a Layout button.
Click that button
Drag the MFName button to the row field
drag the StockName button to the column field
drag the Units to the data field
If the units doesn't say "sum of", double click on it and choose Sum of

And finish up the wizard.

You may want to swap the row field and the stockname position

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx