How to determine Nth occurence in a column?

Richard Illyes
Richard Illyes Member
edited December 2023 in General

Hello.

Do you know of any function or combination of functions that can achieve a new column in the data as the "occurrence" column in the screenshot below?

I would like to know if the ID in the first column is the 1st, 2nd ... Nth occurrence in that column. Is there a way to achieve this?

Thanks in advance.

Richard

image.png


Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Richard

    There are two similar values that you could calculate more easily

    1. the total number of occurrences for each ID using a Group
    2. the First or Last occurrence of each ID using a Lookup step

    Assuming neither of those meet your needs I think it would be possible using multiple functions that utilize the Function Get cell however it would require the data to be pre-sorted (by ID in this case), would that be an issue for any subsequent operations?

  • Richard Illyes
    Richard Illyes Member

    Hi Josh,

    Thanks for your comment. The aim is to assign a new, unique sequential ID based on the original ID, so sorting won't be an issue.

    After posting the question we've been experimenting a bit with Get Cell as you suggested and what seems to work is the following:

    1. Sort
    2. Mark the first appearance of a value in a new column (with an X) and also add CurrentRow in another
    3. Do a lookup with this table on itself, by returning the Row number where we have the ID and the mark
    4. Subtract the (LookupResult-1) from the CurrentRow to get this sequential numbering
    image.png


Categories