How many times each item repeats in a list

1

I need to show how many times each item from a list repeats itself. Something like this:

image
image492×607 14.9 KB

And below the embedded doc of the example above:

The solution probably involves the use of FormulaMap, but I can’t get it right… Does someone have a clue?

2

hi @Carlos_Roriz_Amplus_Construtora

something like this:

image
image2420×1054 180 KB

and for the part II:

thisTable.Filter(Attribute.Contains(thisRow.Attribute)).Count()

I could not open the doc, so you see a screenshot from me.
cheers, best, christiaan

4

Thanks for the reply, but I think I wasn’t clear enough. What I need is the column highlighted in yellow (I created it by typing it, but I wanted to create it using a formula), that shows, for each day, a list of unique attributes associated with each person listed in the name column with its respective number of repetitions. Something like:

  • A (x2)
  • B (x1)
  • C (x3)
  • D (x4)

image
image494×851 22.3 KB

I fixed the doc sharing settings. Now it should be possible to be played with in the embed.

5

This was a fun puzzle. You have to remove bulletedlist() on your [Attribute] and [Unique List] columns so they can be parsed as lists/arrays. Here’s the solution @Carlos_Roriz_Amplus_Construtora :

thisRow.[Unique List].FormulaMap(
  WithName(CurrentValue,uniqueLetter ,
  WithName(thisRow.Attribute.Filter(CurrentValue=uniqueLetter).count(),letterCount,
    concat(uniqueLetter," (x",letterCount,")")
  ))
).BulletedList()
2 Likes
6

Thanks a lot, @Johg_Ananda !!! I’m still trying to wrap my head around this nested WithName(), but it works nicely… LOL

THANK YOU !!!

1 Like
7

OK here’s how to think about it. You’re looking for the count of each letter in the [Unique List] column, or the count of each unique letter, uniqueLetter.

For each unique letter, we want to filter the column [Attribute] and count the number of times the unique letter appears, letterCount.

So FormulaMap() says for each uniqueLetter in [Unique List] lets do the following:

Take the [Attribute] column, and check each value in it to see if it matches the uniqueLetter. Count the matches. I do that with:
Attribute.Filter(CurrentValue=uniqueLetter).count()

WithNames() allows us to break up each step and convert the formulas to variables that are easier for our minds to deal with.

I use uniqueLetter and letterCount because they represent the pieces we need to combine to the solution.

It also overcomes a technical problem when we are looping in a loop. Coda uses currentvalue as your marker in the loop, but when you go into a second loop you get a collision. WithName() avoids this.

Representing the formulas as variables also makes downstream code cleaner and easier to understand. The formula:

concat(uniqueLetter," (x",letterCount,")")

is beautiful in that it works technically and reads literally.

Let me know if there’s anything else I can clear up.

5 Likes
8

very well explained @Johg_Ananda !

1 Like
9

You didn’t need to go through all the trouble to also explain your formula, but I really appreciate that! It’s clear now. Thank you!

1 Like