How to count recurring strings and order them in a List

[rathbala]

Hi all

I have the below columns in a Google Sheet table:

• User
• Action (an action from 5 pre-set actions)
• Scenario (from a large ever-growing list of scenarios)

I want to display an inline List component which shows, for that User:

1. A list with the 5 Actions as headings
2. The scenarios summed up and ordered so that the most frequent scenario is at the top, grouped up by the different Actions

To make this easier to understand, please see a screenshot below.

I’ve been trying to nail this in Glide / Google Sheets but I keep going around in circles, so I’m hoping someone can tell me this is really easy to do haha. Any assistance would be great!

I’ve been able to create the inline List with groupings, but the scenarios are repeating themselves and don’t roll up into single values.

I think the answer is related to something like the below (but I got a little lost trying to replicate this, admittedly):

Can we create an array COUNTIFs formula within Glide

Here is one way to do that: Create a template column that concatenates the values in every row of the two columns you are interested in. For example, lets say its a User table and you are interested in Age & Sex, then your template column would be something like ‘Age:Sex’, and then substitute the actual values in each row Next create a similar template column, but using the actual values you need to count. Again, for example, lets say you are looking for 10 year old males, then it would be “10…

Thanks in advance.

[ThinhDinh]

Let’s say for Action A, do you have to show just the counts for S8 and S1, and not the actual actions itself on the “inline list”?

[rathbala]

I’d like to be able to show ‘Action A’ as a grouping, and then the counts for S8 and S1 within that grouping.

So essentially…

Action A

• S8 2
• S1 1

Action B
etc

I believe the Actions can just be grouped up using the ‘Group by’ option. However rolling up the counts for scenarios, and showing them just once, seems to be where I’ve gotten stuck I get something like the below:

Action A

• S8 2
• S8 2
• S1 1

[Darren_Murphy]

Is it essential that the end result be an inline list?
Could it just be something like a Rich Text/HTML summary table, for example?

Can you provide a copyable version of your sample data?
If I can get that, I’ll have a play around with it.

Just looking at it, I’m thinking that you probably need to do a double transposition. Once for the Actions, and again for the Scenarios.

[Darren_Murphy]

@Darren_Murphy

Can you provide a copyable version of your sample data?
If I can get that, I’ll have a play around with it.

Never mind about that, I just re-created it. Here is a walk through of one possible solution:

Copyable App: https://summarise-items-wr9j.glideapp.io/

JavaScript code to get the scenario counts:

var arr = p1.split(', ');
var counts = {};
for (var num of arr) {
  counts[num] = counts[num] ? counts[num] + 1 : 1;
}
var str = '';
Object.keys(counts).forEach(function (item) {
    str += item + ": " + counts[item] + "<br>";
});
return str;

[rathbala]

Wow Darren! Thank you so much for this, this is legendary.

I guess I presumed incorrectly thinking this may be an ‘easy’ thing to do in Glide, since it needed a bit of JS to make some of the magic happen haha.

I’ll give this a go and let you know if I get stuck, but this seems like a very thorough walkthrough.

[Darren_Murphy]

You’re welcome, happy to help

@rathbala

I guess I presumed incorrectly thinking this may be an ‘easy’ thing to do in Glide, since it needed a bit of JS to make some of the magic happen haha.

I’d say it could be done without the JavaScript, but you’d probably need a secondary helper table. I just got a bit lazy

[rathbala]

Haha completely understandable!

Using a similar example, the dream was to do something like the below with an inline list (without the duplicated ‘idea’):

I’m thinking that if I’m to use the rich text output, I may need to use some CSS to format it to make it look pretty enough?

[Darren_Murphy]

ah, I see.
Okay, that inline list presentation is definitely doable. Essentially you want grouping by Action.
Just requires a slightly different approach.
I’ll have a think about it.

[ThinhDinh]

I think one of the methods is as below:

Step 1: Create a template joining the “title” and the “rating”. E.g: Good - coffee, Great - idea.

Step 2: Create a multiple relation from the template above to itself.

Step 3: Create a single value column returning the rowID for the first match from the multiple relation above.

Step 4: Create a rollup column to count the number of matches for the multiple relation.

Step 5: Create an inline list, filter by rowID being the same as the “first match” rowID generated through step 3 (so there are no duplicates). Group the inline list by the “Rating” (Good/Great), show the title and the rollup count.

[Darren_Murphy]

That works a treat, and is way simpler than my approach

Only thing that needs to be changed is to include the user email in the template (because he also wants to filter by user), and then also include that as a secondary filter on the inline list.

@rathbala - I have added a second tab to the concept app with an implementation of @ThinhDinh’s logic.

[ThinhDinh]

Great. Thanks for the demo!

[rathbala]

Thank you @ThinhDinh for that solution! And @Darren_Murphy for providing the tweak and demo!

This is perfect. I’ve learned a lot in the process too