Forum Discussion
How to restart numbering of ID column from 0
I have an ID column with IDs as such:
1900
1900
2345
2345
3209
3209
3209
4325
4325
4325
I would like to restart this numbering from 0. For example, 1900 would be 0 and 2345 would be 1
0
0
1
1
2
2
2
3
3
3
Are there any formula's I can use to do this quickly?
=COUNT(UNIQUE($A$1:A1))-1=BYROW(A1:A10,LAMBDA(row,COUNT(UNIQUE(A1:row))-1))With Office 365 or Excel 2021 or Excel for the web you can try these formulas.
I think this was posted a week ago. Here's my take:
=LET(uArr,UNIQUE(arr),XMATCH(arr,uArr)-1)If you work with an older version e.g. Excel 2013 you can use a helper formula which is in cell C2 in the example:
=IF(B2=B1,C1,C1+1)And this is the formula in cell D2:
=C2-1Both formulas can be filled down as required.
