Filter S&P500 companies

[oseroser]

Dear,

For my thesis i need to filter a number of S&P500 companies over the last 12 years.
In the list you can see a year, a company name and the CEO-compensation.
But i only need the companies from who I have data every single year.

So my question is the following:

How can i filter my document so that it only reveals the companies who are listed every single year from the year 1995 up until 2007?

If you want to c the document:

http://www.MegaShare.com/3222931

Password: thesis



Thank you!

[Marcol]

Why not use the forum facilities to post your workbook, or a sample from it?

[oseroser]

here is the sample.

so i only need the companies who are constantly there between 1995 and 2007.

The complete workbook is 30mb so i dont think i can attach this here...

[Marcol]

Try this workbook

Your Column "Fiscal" is better if formatted General rather than Text.
To change this in your real workbook
Highlight the column Then Text to Columns > Click "Finish"

Hope this helps

[oseroser]

Dear Marcol, what you did was absolutely brilliant. Unfortunately, I am too noobish with excel to copy what you did. it looked really complicated

I hereby filtered my document already for the biggest part. So i wonder if you could do again what you just did?? It would be a great help if that is possible!

Thanks!


Robbie van Beurden

[Marcol]

Have a look at the attached.

Step 1/. Highlight Column "Fiscal Year" (C) then use Text to Columns to change the Text Value to Numbers.
Text to Columns > "Click Finish"

Step 2/.
In a spare Column, I have used Column P, List the years you need to calculate.
Then in a conveniant cell sum these values (O2)

Step 3/. In L2

=IF(A2<>A1,IF(SUMIF(A:A,A2,C:C)=$O$2,E2,""),"")

Drag/Fill Down
This assumes that your data is sorted on "Company ID Number" and each ID has no duplicate years.
You now have all the company names that have entries for each of your required years.

You could if you wish just filter out the blanks in this column and you get the result you need.
However this does it for you.
Step 4/. In M2 this array formula ( Must be confirmed with Ctrl+Shift+Enter NOT just Enter

=IF(ROW()-ROW($M$3:$M$1001)+1>ROWS($L$2:$L$1000)-COUNTBLANK($L$2:$L$1000),"",INDIRECT(ADDRESS(SMALL((IF($L$2:$L$1000<>"",ROW($L$2:$L$1000),ROW()+ROWS($L$2:$L$1000))),ROW()-ROW($M$2:$M$1000)+1),COLUMN($L$2:$L$1000),4)))

Drag/Fill Down
This removes the blanks from Column K

Step 5/. Conditional Format optional ..... See the workbook.

Hope this helps