topic Re: How do I generate a numbering column that resets based on other column values? in Discussions

How do I generate a numbering column that resets based on other column values?

WetlandWoman — Sat, 10 Jun 2023 23:53:50 GMT

Hi everyone,

I am trying to generate a column that will count the quadrats (single sample area) for a given site on a particular date. I have a Site Code column and a Date column, as well as Latitude and Longitude columns, where a change in latitude and/or longitude equals a new quadrat for that site. Within each quadrat all species were sampled, meaning each row with the same site, date, latitude and longitude has a new species code. I have been able to create a rank column that resets when these four columns change (i.e. site code, date, latitude and longitude) which gives me a count of the number of species that were found at each quadrat. Is there a way to use any of these columns to auto-number when a quadrat changes (integer starting at 1 and increasing by 1) in a site and resets the numbering when the site and/or date changes. I have attached an example file with a subset of my data, and a column called Quadrat that I manually populated with what I am trying to achieve.

I am using JMP version 16.2.0. Thank you so much in advance for your help!

Re: How do I generate a numbering column that resets based on other column values?

jthi — Sat, 03 Sep 2022 17:38:10 GMT

I think you could use something like this:

Col Cumulative Sum(If(Row() == Col Min(Row(), :Date, :Latitude, :Longitude), 1, 0), :Wetland Code)

Re: How do I generate a numbering column that resets based on other column values?

WetlandWoman — Sat, 03 Sep 2022 21:44:10 GMT

Hi @jthi! This is very nearly what I wanted, thank you so much! I just had to create a concatenate column with Wetland Code and Date (called Sampling Code) to pick up when the site or date changed but the other variable stayed the same and it worked!

Col Cumulative Sum( If( Row() == Col Min( Row(), :Date, :Latitude, :Longitude ), 1, 0 ), :Sampling Code )

Thanks so much again for the help :)