Visitor

# 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?

3 Replies

# Re: How to restart numbering of ID column from 0

``=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.

# Re: How to restart numbering of ID column from 0

I think this was posted a week ago. Here's my take:

``=LET(uArr,UNIQUE(arr),XMATCH(arr,uArr)-1)``

# Re: How to restart numbering of ID column from 0

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-1``

Both formulas can be filled down as required.