How to group excel items based on custom rules?

excel, excel-formula, vba

Solution

Ok, I changed your sheet a bit....

Supposing all your data was in cells A1:C9, then you had the following table in cells F1:G5

Search_For:    Category:
laptop         Laptop
iphone         Phone
galaxy         Phone
notebook       Laptop

Now, in cell D2, put in the following formula:

=IFERROR(INDEX(G$2:G$5,MATCH(TRUE,ISNUMBER(SEARCH(F$2:F$5,A2)),0)),"other")

And enter it as an array formula Meaning, once you enter it, hit CTRL+SHIFT+ENTER.

You can then drag the formula from cell D2 down and it should give you the desired results (and you can, of course, increase the list in columns F & G as necessary).

Hope this does the trick!!

Problem

I have a set of data (webmaster tools search queries) which is in excel with the following header: `Query | Impressions | Clicks | Date` Sample google spreadsheet here. I want to add in an extra column called `Category` and categorize all the queries according to custom rules that will search for a string on column A. Ex: `if A2 contains the string 'laptop' then write 'laptop' on the category next to it` So far I have tried a formula to do this but I'm not sure this is the easiest way. Also, if there are lots of categorization rules the formula gets really long and unmanageable. ``` =IF(ISNUMBER(SEARCH("laptop",A2)),"laptop", IF(ISNUMBER(SEARCH("notebook",A2)),"laptop", IF(ISNUMBER(SEARCH("iphone",A2)),"phone", IF(ISNUMBER(SEARCH("galaxy s",A2)),"phone", "other"))) ``` Can you suggest a better way of doing this where I can have the rules in one sheet in this format: `Query_contains | Category_is` where `Query_contains` would be the string that needs to be matched in column A from the initial sheet the and `Category` would be the value that needs to be filled into column D.

Original source