Counting the number of times a word appears in a list

[Hannah_Krumholz]

Hi All,

I am trying to count the number of times a word appears in a list - pictured below. Does anyone know what the formula would be for that? Thank you for the help!

Best,
Hannah

[Pch]

Hi @Hannah_Krumholz

How do you get your list in the table ? … And what is the formula in your Column 3

[amin_jebeli]

@Hannah_Krumholz
if you share the link of your doc, we can help you better but i think you can do it with this formula:

Lookup(thisTable,thisTable.reaserch method , thisRow.reaserch method).Count()

[Hannah_Krumholz]

I have a main table that has a bunch of rows with associated Research Methods

Then I created a list of all those methods and now in the new table I want to count how many times the designated research method is listed in the list

You can ignore column three because that’s the column I was trying to write the formula in to count the list

[Pch]

From what I can see in your last screenshot, I think the easiest way would be to change a little bit your list formula to get an actual list vs. a “ToTexted” list of lists (as from what I can see, this is what seems to be happening there )…

So instead of using List() you could use ListCombine() (to flatten the list of lists) and then count what needs to be counted .

Depending if you need the list to be visible or not you could use a formula looking like this in your List column :

[Research Library Master...].[Research Method].ListCombine().[Research Method]

This should give you your list of all methods .

Then, in your Column 3 you could have a formula looking like this :

thisRow.List.Filter(CurrentValue = thisRow.[Research Method]).Count()

If you want an “all-in-one” formula (i.e.: without showing the list) you could use something like this :

[Research Library ...].[Research Method].ListCombine().[Research Method].Filter(
  CurrentValue = thisRow.[Research Method]
).Count()

I didn’t get the time to produce a sample, sorry … But if you need more explanations, if something is not clear enough (or if it doesn’t work ), don’t hesitate to come back to this post .

In the meantime, I hope this helps !

[Hannah_Krumholz]

That worked!!! Thank you so much!

[Pch]

Ah ! I’m very glad to know it worked !

[Bill_French]

@Hannah_Krumholz

now in the new table I want to count how many times the designated research method is listed in the list

I’ve seen this desired pattern many times and the solution is always a bit complex. In my firm, we do this all the time - looking for basic counting metrics to render conclusions and ideally in charts. To overcome this complexity, I decided to create a custom Pack - a simple formula that given a list of terms (or numbers), counts the occurrences and places the results in an adjacent column.

Countify(
  <term>,
  <list>
)

The pack itself is amazingly simple; Coda has found a way to make me look like a genius.

/*

   ***********************************************************
   SmartPacks: Countify
   Copyright (c) 2021 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************

*/

//
// import the packs sdk
//
import * as coda from "@codahq/packs-sdk";

//
// create the pack
//
export const pack = coda.newPack();

//
// add the smartify phone number
//
pack.addFormula({
  
  //
  // pack name and description
  //
  name: "Countify",
  description: "A formula that counts occurrences of values in a field.",

  //
  // result type
  //
  resultType: coda.ValueType.Number,

  //
  // parameters
  //
  parameters: [
    coda.makeParameter({
      type: coda.ParameterType.String,
      name: "<value>",
      description: "The value to test in a column."
    }),
    coda.makeParameter({
      type: coda.ParameterType.StringArray,
      name: "<column>",
      description: "The column containing the values to be tested for occurrences."
    }),
  ],

  //
  // execute CountifySelect
  //
  execute: function ([thisText, thisList]) {
    return thisList.filter(x => ( x.toString().toLowerCase().indexOf(thisText.toString().toLowerCase()) > -1 )).length;
  },

});

[Gregg_Stebben]

I’m sorry if this is a silly question…but is this pack Countify available to all users? If so, how do I gain access to it? I went to Packs in my document and searched for it…but I do not see it. I have not used Packs much in the past, so I realize this is probably operator error…

Thanks for help!

Gregg

[Bill_French]

@Gregg_Stebben

is this pack Countify available to all users?

Hi Gregg,

I have not made this into a distributed/shared Pack because this code was created before Packs were released to the general public. However, I shared the code, so this is an ideal moment for you to learn how to make a Pack. Use the Pack Studio and you’ll have this Pack running in no time.

And there are some great Pack experts here to help you if you get stuck.

[Gregg_Stebben]

Thank you for making the Pack and sharing it, and thank you for pointing me in the right direction!

[Bill_French]

You bet - and you will undoubtedly encounter a few errors when dropping this code into the Pack editor and building it because I think there were a few basic changes that Coda made since this code worked. But the build process will call these changes out and should provide the fixes needed.

Don’t be intimidated by Javascript or the various sections in a Pack. Countify is an ideal learning Pack because it’s quite simple. Many people are here and eager to help you #low-code like a rock star with Sing Sharp.

[Gregg_Stebben]

I don’t get intimidated easily I’ve learned a ton while using Coda! Thank you again!!

[Agile_Dynamics]

Here is a non-pack solution using just Coda formulas (see 2nd page for ‘how it works’)

[Agile_Dynamics]

this is an explanation of how those formulas work

input is just a quoted string of text (taken from a Coda documentation page)

text is a list of words, created by this formula (the //comments are added by me for explaining)

input
  .Split('.').Join('')                 // remove full stops
  .Split(',').Join('')                 // remove commas
  .Split(' ')                          // split into list of words
  .FormulaMap(CurrentValue.Lower())    // make each word lower-case

words is a list of the unique words in text produced by this formula

text.Unique.Sort()        // extract only unique words then sort them

counts is a list of the counts for each unique word in words as follows

words.FormulaMap(             // for each unique word
  CurrentValue.WithName(W,    // call it W
    text.Filter(              // filter the list 'text'
      CurrentValue=W          // selecting items that match W
    ).Count()                 // returns a list of matching words
  )                           // so we get the count (size) of that list
)                             // which returns a list of word-counts

and finally we list all the unique words and their counts as follows

Sequence(1,words.Count())       // create index from 1 to number of words
  .FormulaMap(                  // loop with index as currentvalue
    words.Nth(CurrentValue)     // print the indexed word
    +'='                        // followed by an equals sign
    +counts.Nth(CurrentValue)   // followed by the indexed count
).BulletedList()                // and show it as a bulleted list