problem with searching and matching

Dear Sir/Madam,

I come across two questions. I hope I could get help from you.

I am currently handling a very large data set, which contains a large number of firms (over 10000), and different products produced by each of the firm.

I need to match the firm's ID with the status that indicates whether the range of products produced by each firm falls into the list of products pre-specified.

I hope I could get help to find the most effective way to made it. This is my first question.

Each firm has different types of products. But my original data set has one line for each firm ID (as seen in the first column) and multiple lines of products for the same firm ID (as seen in the second column).

I hope I can create a excel worksheet which does not have gaps between firm's ID, i.e. for the firms who have multiple products, I could have firm ID-each product on the line by line basis.

I need this kind of format since I am going to add new information into the data later on. some of new information will be firm-specific.

This is the second question.


I enclose the worksheet (the data is in the first worksheet). Product category code in Column I is the so-called pre-specified product code.

I hope to hear from you soon ,

Thanks
Tianshu