excel - Count and Highlight keywords within phrases -

i have excel sheet 2 columns. first column key phrase , second messages. key phrase may occur in messages column. need know how many times key phrase has occurred in messages column. please suggest , easy way of finding.

the key phrase 1 column , messages second column. messages column combination (concatenation) of 1 or more 1 key phrases. need find out how many key phrases each message contain.

it's possible might able collect efficient count module sub procedure performs of maths in memory arrays¹ , returns counts worksheet.

      

i've used standard lorem ipsum keywords , phrases create above sample data.

tap alt+f11 , when vbe opens, use pull-down menus insert ► module (alt+i,m). paste following new module code sheet titled book1 - module1 (code).

option explicit  sub count_strings_inside_strings()     dim rw long, lr long     dim k long, p long, vkeys variant, vphrases variant, vcounts variant      redim vkeys(0)     redim vphrases(0)      worksheets("sheet1")   '<~~ set correct worksheet name\         'populate vkeys array         rw = 2 .cells(rows.count, 1).end(xlup).row             vkeys(ubound(vkeys)) = lcase(.cells(rw, 1).value2)             redim preserve vkeys(ubound(vkeys) + 1)         next rw         redim preserve vkeys(ubound(vkeys) - 1)          'populate vphrases array         rw = 2 .cells(rows.count, 2).end(xlup).row             vphrases(ubound(vphrases)) = lcase(.cells(rw, 2).value2)             redim preserve vphrases(ubound(vphrases) + 1)         next rw         redim preserve vphrases(ubound(vphrases) - 1)         redim vcounts(0 ubound(vphrases))          'perform counts         p = lbound(vphrases) ubound(vphrases)             k = lbound(vkeys) ubound(vkeys)                 vcounts(p) = cint(vcounts(p)) + _                     (len(vphrases(p)) - len(replace(vphrases(p), vkeys(k), vbnullstring))) / len(vkeys(k))             next k         next p          'return counts worksheet         .cells(2, 3).resize(ubound(vcounts) + 1, 1) = application.transpose(vcounts)          'run helper procedure blue|bold of found keywords within phrases         call key_in_phrase_helper(vkeys, .range(.cells(2, 2), .cells(rows.count, 2).end(xlup)))      end end sub  sub key_in_phrase_helper(vkys variant, rphrss range)     dim p long, r long, v long      rphrss         r = 1 rphrss.rows.count             .cells(r, 1) = .cells(r, 1).value2             v = lbound(vkys) ubound(vkys)                 p = 0                 while cbool(instr(p + 1, .cells(r, 1).value2, vkys(v), vbtextcompare))                     p = instr(p + 1, .cells(r, 1).value2, vkys(v), vbtextcompare)                     debug.print vkys(v)                     .cells(r, 1).characters(start:=p, length:=len(vkys(v))).font                         .bold = true                         .colorindex = 5                     end                 loop             next v         next r     end end sub 

you may have rename worksheet processed in 5^th code line. i've included helper routine identifies key words within phrases blue|bold font. comment out or delete call key_in_phrase_helper(...) line @ bottom of first sub procedure if not desired.

tap alt+q return worksheet. tap alt+f8 open macros dialog , run sub procedure. if data resembles sample data i've put should have similar results.

      

---

¹ _{these advanced methods feel best way tackle problem. if have specific questions own research not adequately explain, try address them in comments section. sample workbook created create solution can made available on request.}