I have a table where Col1 are categories. I want a column that ranks the number of items in each category. If there are 20 lines, Col1 has 4 different values. There are 5 rows in each category. I want each of the 5 lines to be numbered.
I’m guessing this is the setup you’re looking for:
[RowID]
Type: Number
Formula: RowId(thisRow)
[Rank]
Type: Number
Formula: Rank(RowID,thisTable.RowID,true)
[Rank in Category]
Type: Number
Formula: Filter(thisTable,Category=thisRow.Category).Rank.Find(thisRow.Rank)
I know you mentioned 5 rows for each category, but I wanted to make sure it worked after deleting rows and with different numbers of rows in each category.
If you change the order of the rows, this will number them top to bottom each time, so the [Rank in Category] of the row can change if you move it’s position.
2 Likes
If you need the original [Rank in Category] to stay the same, you can add Sort() to the formula for that column:
[Rank in Category]
Formula: Filter(thisTable,Category=thisRow.Category).Rank.Sort().Find(thisRow.Rank)
1 Like