Data mining - Trying to create a sheet with keywords (rows) that show how many times they appear and where

[Travis_Johnson1]

I have a decently large data set, and I am trying to mine from it information to see what has value. On that I am trying to create a new sheet that has a list of keywords. I would like to then have a column next to each keyword that displays how many times that word appears in my main sheet (or a single column).

I have seen several topics that cover something similar but nothing that seems to fit the bill.

Can anyone help? Thanks!

[Bill_French]

Travis_Johnson1:

trying to create a new sheet that has a list of keywords.

Yep, this is not really practical in formulas or roll-ups unless it is a very limited set of terms, so that leaves you with two options -

1. Block Scripts (that are run manually, but internal to the base)
2. API-based Apps (that are run automatically, and external to the base)

In each case, javascript is generally powerful enough to extract the keywords and organize them into new data tables/records or even dashboards. However, the biggest challenge is understanding the definition of a keyword.

Imagine all these other challenges were solved - describe what a “keyword” is and the nature of the content you need to extract such keywords from. An example would be great to see.

[Travis_Johnson1]

Thanks for the quick response! :slightly_smiling_face:

The keywords I have. Want to create a sheet where I can add a change the list of Keywords and then have the table update with the number of appearances of that keyword. So for example

Column A (Keywords) Column B (appearances)
Acme 1250
Beta 900
Charlie 650
etc…

[Bill_French]

Travis_Johnson1:

Want to create a sheet where I can add a change the list of Keywords and then have the table update with the number of appearances of that keyword.

Okay, good to know - two more questions:

1. Manual or automated?
2. Example of the content being searched for the existence of keywords? Is it a rich-text field? A Long string? Attached document?

[Travis_Johnson1]

Keywords are 100% manual. But would prefer the number of appearances to be automated. So add in new keyword via copy and past (or type) and column B out puts the number of times that keyword is mentioned. :slightly_smiling_face:

[Travis_Johnson1]

Right now the list of Keywords are single string with 90% being a single word. I would be happy with something that only allows single words. But short phrases would be idea.

[Bill_French]

Travis_Johnson1:

I would be happy with something that only allows single words. But short phrases would be idea.

Not an issue - the list of strings could be single words of a string of words; looking for existence is a simple matter of counting string (a) inside content (b).

Travis_Johnson1:

So add in new keyword via copy and past (or type) and column B out puts the number of times that keyword is mentioned. :slightly_smiling_face:

Okay - so now I’m confused. Earlier you said there was a list of keywords and phrases that needed to be tested against each content item presumably in a list of rows where the content being tested is a text field? rich-text field? An attached field?

Now you’re saying something a little different - enter a keyword in col B and test against another [text?] field on that same row?

The former requires a program; the latter requires only a formula field.

[Travis_Johnson1]

Sorry that I wasn’t clear.

First to answer your question: Plain Text field (not rich text)

Second, I think we were on the same page first. I have a sheet that has a long list of rows of data. In that data we have 2 columns which have the keywords I want to count. So for reference Lets call it a 3 column table. First column A has the record identifier (serial #), Column B has a summary (short string of plain text) Column C has a long summary (long string of plain text).

Now ideally I would like to create a new sheet that has 2 Columns (Sheet 2). Column A has a list of Keywords. These are not pulled from that data set. I enter them in to see if the data set has any mentions (call it a search). Column B (sheet 2) to display a number of instances that the keyword from the same row in Sheet 2 appears in Sheet 1 under column A and B combined.

Is that clearer? And huge thanks!

[Bill_French]

Yep - much better. We’re dialing it in.

Okay - I have a few ideas - will need some time to formulate an approach, but I’ll circle back in a while.

[Travis_Johnson1]

Your amazing, where do I send the beer?