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

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.

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

1 Like

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

1. `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

1. `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

5 Likes

@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

1 Like

No problem @Gerardo_Ducos !

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

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