Forum Discussion
Identifying Keywords in a Long List of Descriptions Shortcut
Hi everyone, I have 101,000 descriptions. I am searching for keywords within these descriptions to narrow down a target list. I have 147 keywords I would like to search for. Column one contains the number representing the description and column 2 is the description. I am trying to build a list, whereby searching for the keyword e.g. dog, all the descriptions with the word dog in them will populate a list with the corresponding number that represents them. So far I have used the below formula which has worked; =INDEX(the column with the list of numbers which represent/correspond to the subscription, SMALL(IF(ISNUMBER(SEARCH("dog (the keyword)", the descriptions column)),MATCH(ROW(the descriptions column),ROW(the descriptions column))),ROWS($A$1:A1))). My computer cannot cope with so many calculations across 101k descriptions. It stops responding. Does anyone have an alternative or can offer some advice, which would increase the speed of calculation or offer a solution with fewer steps?
3 Replies
NathanJB665 Perhaps Power Query is more suited for such a data set. I attached a small demo. Have you used PQ before?
Test it by expanding the table on the left with some of your own descriptions and the one on the right with real keywords. But don't change the column headers or table names. Now, from the Data ribbon select Refresh All. Does it work for you?
Thank you so much for getting back to me. I am unaware of Power Query, and have never used it before. Would you mind letting me know how you would go about using it??Thanks again
NathanJB665 It will require a bit of getting used to. The link below would be a good starting point. But there are many others on-line.
