How to change auto number formula when value changes in a column

Hey! It's my first time posting here, and I really hope someone can help me! ๐
I have a formula which I'd like to modify. I will explain what I have and then how I'd want to change it.
I have a formula that auto-numbers indented rows, and resets the count to 1 for every parent row, like this:
โ RowId column that is of type # Auto number
โ Helper column with the formula =MATCH(RowId@row, RowId:RowId, 0), which makes sure the number are counted correctly
โ IsParent which checks if the row has children, with =COUNT(CHILDREN()) > 0
โ finally, Nr column with formula =IF(IsParent@row, Helper@row, Helper@row - PARENT()), which subtracts the value of the parent from the helper column so that children will always start counting from 1.
The problem:
I have two more columns, Name and NameChange:
โ Name is a multiple choice column that can change values between A and B (and possibly C,D etc)
โ NameChange is a column that becomes 1 every time the value from Name changes, or more like when it is different from the value in the cell above it, with the formula =IFERROR(IF(INDEX(Name:Name, MATCH(RowId@row - 1, RowId:RowId, 0))<> Name@row, 1, 0), 1)
What I need is the formula to start counting from 1 every time the Name column changes, (so every time NameChange is 1) like in the picture shown above, where I manually wrote the values of the Nr column.
I don't know how to do this without introducing another parent row every time the Name changes, which is really not ideal. I also want to keep Nr being a column formula, and can create helper formulas if needed, but I don't want to do anything manually because the sheet changes a lot during the day.
If you have any ideas please answer them here and thanks a lot!! ๐
Best Answer
-
Hey!
Thanks a lot for the answer! I had to modify the formula a bit as it wasn't exactly what I wanted, but it really helped me!
I modified it like this for Nr: =IF(IsParent@row, Helper@row, Helper@row - PARENT() - COUNTIFS(Name:Name, <>Name@row, Helper:Helper, <=Helper@row, [Parent Row]:[Parent Row], =[Parent Row]@row))
What I needed was the following: keep count of how many rows with the same Name were already indented under the same parent. I did it by subtracting the no. of rows that didn't fit the criteria.
I can also remove the Name Change column, it's not used.
Result:
Thanks a lot again! ๐
Edit: posted this comment form the wrong account oops ๐
Answers
-
Hi @Julie Mary
I changed your sheet a bit as I don't know how to indent without inputting some date to name. (I input P, which is grayed out with the conditional formatting.)
I also added a [Parent Row] helper column, which shows the row number of the parent.
Then, I used the SUMIFS function as follows;
[Nr] =IF(NOT(isParent@row), SUMIFS(NameChange:NameChange, Row:Row, <=Row@row, [Parent Row]:[Parent Row], [Parent Row]@row))
[NameChange] =IF(NOT(isParent@row), IF(INDEX(Name:Name, MATCH(Row@row - 1, Row:Row, 0)) <> Name@row, 1, 0))
[Parent Row] =PARENT(Row@row) -
Hey!
Thanks a lot for the answer! I had to modify the formula a bit as it wasn't exactly what I wanted, but it really helped me!
I modified it like this for Nr: =IF(IsParent@row, Helper@row, Helper@row - PARENT() - COUNTIFS(Name:Name, <>Name@row, Helper:Helper, <=Helper@row, [Parent Row]:[Parent Row], =[Parent Row]@row))
What I needed was the following: keep count of how many rows with the same Name were already indented under the same parent. I did it by subtracting the no. of rows that didn't fit the criteria.
I can also remove the Name Change column, it's not used.
Result:
Thanks a lot again! ๐
Edit: posted this comment form the wrong account oops ๐
-
Happy to help!๐
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!