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
@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:
@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!
@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.