COUNT HOW MANY TIMES A VALUE Is CONTAINED IN A WORD

[Ruizsteve]

Hi, I need help with a formula, I have a column with more than 650K search terms and I need to know how many times each word appears in the column whether is the exact word or is being contained in another word. the following is a small example of how the result should look like.

search term	occurrency
iphone	9
iphone 12	1
iphone case	2
iphone case 12	1
iphone 13	1
iphone 14	1
iphone pro max	3
iphone pro max case	1
iphone pro max 14	1

occurrence is the result i need to get and i have the column search term

i would really appreciate your help with this matter. 

Steven

[BS_THE_ANALYST]

@Ruizsteve I'd use an iterative macro for this. I won't lie, this was a bunch of fun. Thanks for the challenge. I'll attach the workflow (and the macro). Beware, this will take a while as you have 650k records.

Input:

Macro:

Macro configuration:

I've configured it to run for 670k. 

Here's what my suggestion is though. On the initial workflow, grab a sample tool, and run it for like 1000. See how long it takes:

[BS_THE_ANALYST]

@Ruizsteve I ran this on a list of 10000. It works at a decent speed. My computer is by no means high-end though!

1 minute for that.

Don't feed any extra columns into the macro though:
Just use a select tool to only take in the search term column. 

Let me know how it goes!

[Qiu]

@Ruizsteve 
I agree with @BS_THE_ANALYST , this would an expensive calculation.

I tried to reduce the append tool which hope can be a bit faster.