Hi, I’m trying to number some Excel spreadsheets with thousands of
rows. Basically, what I’m trying to do is get the numbering to
increase by one every time the first part of the data (the first 10
characters) in column B changes. The numbers after the decimal point
in column B don’t matter as far as the sequential numbering in column
A is concerned. This is how I would like the numbering to look:

Column A Column B
1 AB00015365.001
2 AB00095763.002
3 AB00037241.003
AB00037241.004
AB00037241.005
AB00037241.006
4 AB00028195.007
AB00028195.008
5 AB00036378.009
6 AB00063493.010

I’ve been trying different formulas using various combinations of IF,
COUNTIF, and LEFT but so far I haven’t been successful. Can this even
be done with a formula? Thanks in advance for any help or
information.

HI,
A1 : 1
A2 : =IF(LEFT(B2,10)<>LEFT(B1,10),MAX($A$1:A1)+1,"")
Copy downwards.
Daniel


> Hi, I’m trying to number some Excel spreadsheets with thousands of
> rows. Basically, what I’m trying to do is get the numbering to
> increase by one every time the first part of the data (the first 10
> characters) in column B changes. The numbers after the decimal point
> in column B don’t matter as far as the sequential numbering in column
> A is concerned. This is how I would like the numbering to look:
>
> Column A Column B
> 1 AB00015365.001
> 2 AB00095763.002
> 3 AB00037241.003
> AB00037241.004
> AB00037241.005
> AB00037241.006
> 4 AB00028195.007
> AB00028195.008
> 5 AB00036378.009
> 6 AB00063493.010
>
> I’ve been trying different formulas using various combinations of IF,
> COUNTIF, and LEFT but so far I haven’t been successful. Can this even
> be done with a formula? Thanks in advance for any help or
> information.

Hi, Daniel, thank you so much for your response. I just tried your
formula and it works great! Thanks again!


On Aug 16, 4:08*am, DanielCo <dcolardelle...@free.fr> wrote:
> HI,
> A1 : 1
> A2 : =IF(LEFT(B2,10)<>LEFT(B1,10),MAX($A$1:A1)+1,"")
> Copy downwards.
> Daniel
>
>
>
>
>
>
>
> > Hi, I’m trying to number some Excel spreadsheets with thousands of
> > rows. *Basically, what I’m trying to do is get the numbering to
> > increase by one every time the first part of the data (the first 10
> > characters) in column B changes. *The numbers after the decimal point
> > in column B don’t matter as far as the sequential numbering in column
> > A is concerned. *This is how I would like the numbering to look:
>
> > Column A * * * * * * Column B
> > 1 * * * * * * * * * * * * AB00015365.001
> > 2 * * * * * * * * * * * * AB00095763.002
> > 3 * * * * * * * * * * * * AB00037241.003
> > * * * * * * * * * * * * * *AB00037241.004
> > * * * * * * * * * * * * * *AB00037241.005
> > * * * * * * * * * * * * * *AB00037241.006
> > 4 * * * * * * * * * * * * AB00028195.007
> > * * * * * * * * * * * * * *AB00028195.008
> > 5 * * * * * * * * * * * * AB00036378.009
> > 6 * * * * * * * * * * * * AB00063493.010
>
> > I’ve been trying different formulas using various combinations of IF,
> > COUNTIF, and LEFT but so far I haven’t been successful. *Can this even
> > be done with a formula? *Thanks in advance for any help or
> > information.