How to group excel elements based on custom rules?
I have a dataset (webmaster tool searches) that is in excel with the following title:
Query | Impressions | Clicks | Date
Sample google spreadsheet here .
I want to add an extra column named Category
and categorize all queries according to custom rules that will look for a row in column A. Example:
if A2 contains the string 'laptop' then write 'laptop' on the category next to it
So far I've tried the formula for this, but I'm not sure if this is the easiest way. Moreover, if there are many categorization rules, the formula becomes very 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 to do this, when I can have rules on one sheet in this format:
Query_contains | Category_is
where Query_contains
will be the row to be matched in column A from the original sheet, and Category
will be the value to be filled in column D.
source to share
Ok, I changed your sheet a little ....
Suppose 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, enter 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 . The value, when you enter it, press CTRL+ SHIFT+ ENTER.
Then you can drag the formula from cell D2 down and it should give you the results you want (and you can of course increase the list in columns F and G as needed).
Hope this is the trick!
source to share
This little macro assumes your data is in Sheet1 , and your rules are in the rule sheet in columns A and B:
Sub catagorize()
Dim s1 As Worksheet, s2 As Worksheet
Dim N1 As Long, N2 As Long
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("rules")
N1 = s1.Cells(Rows.Count, "A").End(xlUp).Row
N2 = s2.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N1
v = s1.Cells(i, 1).Value
For j = 1 To N2
If InStr(1, v, s2.Cells(j, 1).Value) > 0 Then
s1.Cells(i, "D").Value = s2.Cells(j, "B").Value
End If
Next j
Next i
End Sub
source to share
And for the third option, you can use your own formula.
I created a table for categories only on a separate sheet and then pasted the following code into the standard module.
Option Explicit
Function CategoryLookup(s_Query As String, Keyword_tbl As Range)
Dim rngKeywords As Range
Dim s_foundCategory As String
Dim b_CategoryExists As Boolean
b_CategoryExists = False
For Each rngKeywords In Keyword_tbl
If InStr(s_Query, rngKeywords.Value) <> 0 Then
s_foundCategory = rngKeywords.Offset(0, 1).Value
b_CategoryExists = True
Exit For
End If
Next rngKeywords
If b_CategoryExists = True Then
CategoryLookup = s_foundCategory
Else
CategoryLookup = "Other"
End If
End Function
Then in D2
(your category column) paste the following formula (which can be dragged and dropped)
=CategoryLookup(A2,categories!$A$2:$A$5)
source to share