Count of each item in a set without using JavaScript

Imagine that I have a joined list of one or two character codes that looks like so:

N,N,N,N,O,D,N,N,N,N,N,O,D,N,N,N,N,N,O,N,N,N,N,SB,O,D,D,SB,N,N,N

And what I want to do is get a count of each code in the set, so my end result should be two columns, one containing each unique code and the other containing a count of that code, like so:

Screen Shot 2022-08-04 at 2.04.07 PM

My current solution works as follows:

  • A Split Text column to coerce the list into an array
  • A Unique Elements plugin column to get an array of unique codes
  • A Single Value column to transpose the unique codes into a list spanning multiple rows
  • A JavaScript column that takes the original list plus the unique code for each row as inputs, and returns the counts:

I feel like it should be possible to do this without having to resort to JavaScript, but I haven’t managed to find a way. You’d think that a simple relation + rollup should do the job, but I haven’t been able to find a way to create a relation that doesn’t get the same number of matches for every row.

Any one have any clues?

1 Like

Hi Darren,

How about using only two columns and a roll-up column like this?

Thank you

Have a great day!

P.S : Maybe the sound will not be good because me and my family was sicked past couple days and came back home yesterday night but have some cough. Hope you can listen that well! :slightly_smiling_face:

5 Likes

Very clever, well done :+1:

I guess I didn’t see this option because I was too hung up on the idea of relation + lookup.

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.