Complex Excel Question: Entropy Measure of Diversification in Excel (Example from SAS)

Hello,

Currently I'm writing my thesis on diversification in M&A. Therefore I am using the 'Compustat segment tapes'.
In short, this database has data on the sales on a division/segment level per company. For the different segments a NAICS (industry code to indicate what are the primary activities within the segment) is awarded/provided.

The data for a specific company (in this case: Microsoft) can look as follows:

Schermafbeelding 2015-11-11 om 15.08.26.png

So there are 6 segments identified for Datadate = Sourcedate = 20130630. For 'Corporate & Other' no NAICS code is provided.
Entertainment & Devices has NAICS Code 339930 and the other 4 segments have NAICS code 511210

For my research I want to distinguish between diversifying versus non-diversifying acquisitions. To distinguish between these two types of acquisitions I want to use 'The Entropy Index of Diversification'
For a description of this technique, see: https://books.google.nl/books?id=31o...egment&f=false

On the WRDS a code has been provided to construct this index in SAS. See: http://www.wrds.us/index.php/forum_w...read/290/#1137
As my university does not work with SAS, and I can't code in SAS, I prefer to do this in Excel.

So, in short I was hoping you could help/assist me to construct an excel code to be able to have a good starting point for my thesis.

I am able to construct this all by hand, but with 800 companies this will take too much time.

I think I will need a function that
a) identifies the unique companies
b) only selects the segments sales for which Data Date = Source Date
c) constructs total sales as the sum of identified segments (excluding the Corporate & Other segment in the Microsoft example)
d) Adds up the sales in segments with a similar NAICS code

Thank you for helping me out. I'm not a real star in Excel/Stata/Etc.

Many members are unable to see images in the *.png format.

If you need to post an image post it in the *.jpg format.

Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.