Help: How to increment numbers by rows based on column criteria?

[Gerardo_Ducos]

Hi Codans,

I’m developing an annual planning tool for a multi-country project to be used by a team of 20 persons.
Each person will select a key activity from a list and the country for which that activity is being planned.
The list of activities are numbered as follows: 1111, 1112, 1113, 1121, 1122… etc.
I want to have a column that counts the number of sub-activities under each activity, and the numbering restarting at 1 for each country. Something like this
For country A:
Activity 1111
Sub-activity 1111.1
Sub-activity 1111.2
Sub-activity 1111.3
Sub-activity 1111.4
Activity 1121
Sub-activity 1121.1
Sub-activity 1121.2

For country B
Activity 1111
Sub-activity 1111.1
Sub-activity 1111.2
Sub-activity 1111.3
Sub-activity 1111.4
…

What I can’t figure out in Coda (although I managed to do it in Excel) is how to increment the counter (the decimal part) at each line while the country and the Activity remain the same and then start at 1 if any of these change.
Any ideas or help on a formula for this will be truly appreciated.

Many thanks in advance

[Christiaan_Huizer]

hi @Gerardo_Ducos ,

the counting

I am not a Codan and understood only partly your question. Does the feedback help?

The increment
it is not comparable with any spreadsheet, but to have something, consider this option, value for new row:

cheers, Christiaan

[Pch]

Hi @Gerardo_Ducos ,

I’m not sure if I understood correctly your setup but here’s what I have

To create an incremental count of something in Coda, you need to have an increment somewhere we can use to compare rows between each others …
This can be done by creating a row index/number (which is what I did here) or another row property such as (Modified / Created (which I don’t have the time to explore here ))

In the sample below, you’ll see that I added a column (called [Row Number]) which find thisRow in the Table (quite literally) :

thisTable.Find(thisRow)

Then, I added another column called [Sub-Activity Decimal] which is just there to create the incremental count I think you’re looking for …
And the formula is :

Table.Filter(
  Country.Contains(thisRow.Country) 
  AND Activity.Contains(thisRow.Activity) 
  AND [Row Number] <= thisRow.[Row Number]
).Count()

And what it does is :
It takes the whole list of rows (each row being stored as CurrentValue) in the table Table ( ) and looks for the rows where :

1. CurrentValue.Country contains thisRow.Country
   (This returns (behind the scene) a 1st list of values)

AND

2. CurrentValue.Activity contains thisRow.Activity
   (From the 1st list of values, Filter() only keeps the rows where this condition is true … Which “becomes” a 2nd list of values)

AND

3. CurrentValue.[Row Number] is less or equal to thisRow.[Row Number]
   From the 2nd list of values, Filter() only keeps now the rows where this condition is true and this “creates” an incremental list of values …
   As for the very first Sub-activity of the very first Activity for Country A it will only find 1 row … 2 for the second activity… etc …

All that’s left is to Count() the values returned by the Filter() to have the incremental count of Sub-Activity per Country and Activity

As for the Sub-Activity column, I created them with this formula :

Concatenate(
  "Sub-",
  thisRow.Activity.Lower(),
  ".",
  thisRow.[Sub-Activity Decimal]
)

Which simply creates the Sub-activity xxxx.x using the various info from the columns I already have
(But there are other ways to get to that result (i.e.: Format())

Now depending on your actual setup (and how you linked your datas to each others or if you did), this might need to be adapted … But I hope this helps

[Gerardo_Ducos]

@Pch,
No wonder you’re a “Community Champion”.

The solution you proposed works flawlessly. Thank you very much for taking the time and sharing!
This will allow me to create a gantt (timeline) where I can have a global overview or a view per country of respective sub-activities with the proper numbering.

Gerardo

[Pch]

No problem @Gerardo_Ducos !

I’m very glad to know this works for you !