BookmarkSubscribe
Choose Language Hide Translation Bar
sornasst
Community Trekker

Formula for automatic counter by group of rows

Hi,

I would like to write a formula in JMP 11.1 that will automatically create a counter by  group of rows as shown in the table below

GroupCounter
ABC1
ABC1
ABC1
ABC1
CDE2
CDE2
EFG3
EFG3
EFG3
GHI4
GHI4
GHI4

I suspect that the Dif() and/or Lag() functions have something to do with this but I have not been able to implement it.

Note: this counter by group of rows is very easy to implement in Excel

Thank you for your help.

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Formula for automatic counter by group of rows

Assuming the data are sorted:

If(Row() == 1, counter = 1); If(:group == Lag(:group), counter, counter = counter + 1); counter

Jim
0 Kudos
2 REPLIES 2
txnelson
Super User

Re: Formula for automatic counter by group of rows

Assuming the data are sorted:

If(Row() == 1, counter = 1); If(:group == Lag(:group), counter, counter = counter + 1); counter

Jim
0 Kudos
Jeff_Perkinson
Community Manager Community Manager

Re: Formula for automatic counter by group of rows

Here's the formula version of Jim's answer.

If( Row() == 1,

1,

Lag( :Counter Formula, 1 ) + (Lag( :Group, 1 ) != :Group)

)

12602_JMPScreenSnapz039.png

Notice that the addition step takes advantage of the fact that comparisons result in a 1 or 0 for true or false. So, we can compare the value of Group in the current row to the previous row and if they are not equal (!=) it will add 1.

-Jeff

-Jeff