Mark and filter the first lines in each transaction and give them a value 0 in an adjacent column.
Step-by-Step Guide
- Identify the Range: Let’s assume your data is in column A, starting from cell A1.
- Insert a Helper Column: Use column B as a helper column to generate the sequence of numbers.
- In cell B1, enter =IF(A1=0, 0, 1).
- In cell B2, enter =IF(A2=0, 0, IF(A1=0, 1, B1+1)).
- Copy the formula from cell B2 down the column as far as needed.
Explanation
=IF(A1=0, 0, 1): This formula initializes the sequence. If A1 is 0, B1 will be 0. Otherwise, it starts the sequence with 1.
=IF(A2=0, 0, IF(A1=0, 1, B1+1)): This formula continues the sequence. If A2 is 0, B2 will be 0. If A1 is 0, it starts a new sequence with 1. Otherwise, it increments the previous number by 1.
This will automatically fill column B with numbers starting from 1 until a zero value appears in column A, then it will reset and start again from 1 after the zero.