cumulative count based on a key.

[Shem]

Hi there,

I have a 'collect key' column and want to provide a number (in sequence) next to it for all trips that have the same key, and for this to repeat for all of the different keys. 

What this is showing is a transport companies trip details.

i.e - "key = xxx"  the first-row occurrence would return 1, the second 2, third 3

- "key =yyy" the first-row occurrence would be 1, second 2 ...etc.

I guess what I am after is a cumulative count for each key.  

I am unsure of how to upload data but below is a sample.. i am looking to create a column that provides information for the right column

collect key	accumulated
11/9/2018TTLCTemuka CT - Clandeboye DS Grid	1
11/9/2018TTLCTemuka CT - Clandeboye DS Grid	2
11/9/2018TTLCTemuka CT - Clandeboye DS Grid	3
11/9/2018TTLCTemuka CT - Clandeboye DS Grid	4
11/9/2018TTLCTemuka CT - Clandeboye DS Grid	5
11/12/2018TTLCTemuka CT - Clandeboye DS Grid	1
11/12/2018TTLCTemuka CT - Clandeboye DS Grid	2
11/12/2018TTLCTemuka CT - Clandeboye DS Grid	3
11/12/2018TTLCTemuka CT - Clandeboye DS Grid	4
11/12/2018TTLCTemuka CT - Clandeboye DS Grid	5
11/13/2018TTLCTemuka CT - Clandeboye DS Grid	1
11/13/2018TTLCTemuka CT - Clandeboye DS Grid	2
11/13/2018TTLCTemuka CT - Clandeboye DS Grid	3
10/26/2018TTLCSCS Timaru	1
10/26/2018TTLCSCS Timaru	2
10/26/2018TTLCSCS Timaru	3
10/26/2018TTLCSCS Timaru	4
10/26/2018TTLCSCS Timaru	5
10/26/2018TTLCSCS Timaru	6
10/26/2018KR-2HJM14SCS Timaru	1

Any help is greatly appreciated.

Thanks, Shem

[AlB]

Hi @Shem,

Do the rows you want to number (with same collect key) have exactly the same values on each column (as it appears from the attached image)?

Does each row have a unique identifier (like a row number)?

If you attached a sample with the table it would probably be easier for people to help.

.

[Shem]

I am unsure of how to attach a sample. Hopefully, the amended post will suffice.

Thanks, Shem 

[AlB]

@Shem

Yeah, you need to use a URL to the file rather than attaching it directly, like to Dropbox or you could upload the file to a website like this

[AlB · Accepted solution]

If you can have an extra column with a unique, consecutive numerical ID (index), I think the code below should work. You can add an index column easily in the query editor (see the solution on this post)

Table[Accumulated] =
COUNTROWS (
    FILTER (
        Table;
        Table[Collect Key] = EARLIER ( Table[Collect Key] )
            && Table[ID] <= EARLIER(Table[ID])
    )
) 

[Shem]

Thanks, it worked perfectly!