turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Formula for automatic counter by group of rows

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 24, 2016 12:07 PM
(2605 views)

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

Group | Counter |

ABC | 1 |

ABC | 1 |

ABC | 1 |

ABC | 1 |

CDE | 2 |

CDE | 2 |

EFG | 3 |

EFG | 3 |

EFG | 3 |

GHI | 4 |

GHI | 4 |

GHI | 4 |

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 24, 2016 1:25 PM
(4990 views)

Solution

Assuming the data are sorted:

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

Jim

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 24, 2016 1:25 PM
(4991 views)

Assuming the data are sorted:

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

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Aug 24, 2016 2:58 PM
(2495 views)

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

If( Row() == 1,

1,

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

)

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