Consecutive Row Numbers?

I know there must be a simple solution to this, but I cannot find it for the life of me.

So, I need to have a column where each row has a unique consecutive number ranging from 1 to however many rows I have. I cant use rowId because if I delete any rows and then add new ones, the number will be wrong.

Such a simple problem, but it is holding nearly all of my docs back from being made correctly :mask:

@zoglow to confirm, you need a unique, consecutive, and static (does not change on deletion/addition) Row ID? The formula =rowID(thisRow) has been working for me to do this.

On the other hand, if you’re looking for a unique, consecutive, and dynamic (changes with delete/add, so that the series is always constant) ID, I think I have it. It’s a little hacky.

In your table, make a column Static Row ID =rowID(thisRow) as above. Then, make a column Dynamic Row ID with the formula:

=thisTable.CountIf(RowId(thisRow) > [Static Row ID]).

This counts the number of rows that existed before this row, essentially. I suppose you could also use one of the metadata functions to complete this, which may be faster. The trick, though, is that you need to store the comparison value in a column in order to use it in CountIf()—to my knowledge, at least.

Is that what you’re looking for?

Thanks so much, that second one seems to have done the trick!

Another option that I use is Rank to do this.

Rank(RowID,thisTable.RowID,true )

13 Likes

Oh, that’s lovely! Thanks!

Is there a way to keep the Rank ID ascending ordered even if you move rows around in the table?

Yes Jonas please use rank column as a column for sort.

2 Likes

That will not cut it as I don’t want the rest of the table to be re-sorted. I need a way to reference the previous row in a table and was hoping to use your Rank formula to create a row index I can reference, but I need to have it always stay absolute top to bottom, independent of if the user sorts the table or repositions rows. Not sure if I make my self clear, but let me know if you can think of another option

I’m building an agenda for a workshop and want to achieve the same result as in this thread Cumulative Start Times for Event Schedule. The user would set the start time and then set duration per activity (row). The formula I need is to populate a Start Time column automatically for each activity (row) calculated on start time + duration of all previous activities (rows). The formula in linked thread above works as a solution, until the user moves a row to a new position in the table.

Does this answer help? we would be happy to work with you if you reach us via in app chat.

Thanks. I will look at this tomorrow as it’s getting late in my time zone (Stockholm). What time zone are you in or do you have 24 hours support?

We are available on weekdays usually between 9AM-5PM Pacific standard Time.

1 Like

Here is a link to the Excel file I’m trying to redo in Coda. It needs to be open in Excel to work as intended. As you will be able to see, the formula in A5-A19 automatically calculates correct start time based on previous rows start time and duration and the start time will continue stay correct if you move a row up or down. Can you think of a way to reproduce this in coda?

https://drive.google.com/file/d/1vi3ty8vjQId9U3G82wIvvino0xuM9BlO/view?usp=sharing

Thanks Jonas for sharing Excel - I understand what you are trying to do now. Here’s a post I made to find visual position of the record as I think it would help others as well -

I think it should be straight forward for you to update start time now that you know visual position. :slight_smile:

Thank you,
Krunal.
Engineer - Coda

2 Likes

Excellent and thanks for quick reply

here’s a document in case if you want to take a look.

4 Likes

Thanks for this. It solved my problem and here’s a version of my final doc https://coda.io/d/Team-Train-Agenda_dddMwh-c5Fi/Agenda_suebT#_luDKo

I still think rowNumber would be useful as a property column besides row Id or maybe just accessable via a formula like rowNumber().

Also something like previous() or next() to access the surrounding rows in the current order would have been useful in math tables on one or the other day.

it feels weird to see the row numbers in grey on the left anyway, but just not be able to use it.

13 Likes

I second @Daniel_Stieber

1 Like

I third Daniel :blush:
This would simplified many operations.

1 Like