problem with searching and matching

[tianshu]

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

[Pete_UK]

I think it is easier to deal with the second question first. Highlight column A by clicking on the identifying letter above the column. Then press the F5 (GoTo) key. Click on Special, then select Blanks, then OK - all the blanks in column A will be selected, with the active cell (shown in a different colour) being A3. You should start to enter a formula by typing = and then click on the cell immediately above the active cell (i.e. cell A2), then hold down the CTRL key and press < Enter >.

Hey Presto! - all those cells will be filled with that formula (i.e. get the value from the cell above), so your companies will be filled into every cell. You might like to fix those values by clicking < copy > | Paste Special | Values | OK then press the < Esc > key.

Hope this helps.

Pete

[tianshu]

Dear Pete,

Yes, thank you so much. I get the solution for the second question!



Tianshu

[Pete_UK]

For your first question, you can put this formula in C2:

=ISNUMBER(MATCH(B2,I$2:I$21,0))

and then copy down. It will return TRUE if the product number does appear in the list in column I, or FALSE if it does not. If you want different words you could do something like this instead:

=IF(ISNUMBER(MATCH(B2,I$2:I$21,0)),"yes","no")

Hope this helps.

Pete

[tianshu]

Dear Pete,

Before I try the suggestion you recommended, I want to highlight one thing I did not clearly indicate in my post. The product category is actually not number but rather text in my real data set.

Do you think your recommendation would also work for such case?

Tianshu

[tianshu]

I am in the middle of reading the explanation of IS, I guess if the product code is text, I should use ISTEXT instead.

Tianshu

[Pete_UK]

No, the ISNUMBER relates to the MATCH function - it returns a number if a match is found.

If the product categories in column I are text values, then those in column B also have to be text in order to get a match. If they are text values which look like numbers (no alpha characters), they you can use B2&"" instead of just B2 in the formula.

Hope this helps.

Pete

[tianshu]

Dear Pete,

Thank you very much.

Tianshu

[tianshu]

Dear Pete,

Seeing you are still online, I hope you can further help me with another question related to matching (maybe I should open a new thread ).

Both the products produced by each firm in my sample and the pre-specified product list are text. Not only so, some time they are spelled in different manner ( a kinds of fuzzy matching and fuzzy look-up).

I am reading the fuzzy look-up and Reconcile-csv now, and try to find out which is the best way to handle this.

I am still in the process of digesting both. Do you have any suggestion for this kinds of situation?

Thanks again,

Tianshu