AutoNumber (1.1, 1.2 ..., 2.1, 2.2 ....)

[marreco]

I need auto number based this way (1.1, 1.2 ..., 2.1, 2.2 ....).

I use formula but something wrong, look my file.

Thank you!!

 

[sudipto3003]

dear marreco,
why using formula......just type 1.1,1.2 and then select the both cells and drag.

 

[marreco]

Sorry, i don't do it, becouse when value delete in sheet (column B), formula automatic change (autonumber) based new orde data.

 

[PaulF]

In your spreadsheet
A5 = 1.1 << Type this in.
A6 = =IF(B6="","",MAX($A$5:A5)+0.1)
Drag down...

 

[marreco]

Hi PaulF, Thanks for reply, but still try get result (see imagem in yellow).

 

[PaulF]

I see the 1.1 to 1.1 increments by .1 then you increment by .01...

Why ??

 

[marreco]

Hey PaulF, sorry, i'm dumb
Try understand my new exemple
I need this result
1.1 chandoo VBA
1.2 chandoo Formula
1.3 chandoo Pivot Table
1.4 chandoo Chart
1.5 chandoo VBA
2.1 PaulF VBA
2.2 PaulF Formula
2.3 PaulF Pivot Table
2.4 PaulF Chart
2.5 PaulF Power Pivot Table
2.6 PaulF DashBoard
2.7 PaulF Solver
3.1 Luke M VBA
3.2 Luke M Formula
3.3 Luke M Pivot Table
4.1 Small Man VBA
4.2 Small Man Formula
4.3 Small Man Pivot Table
4.4 Small Man Chart
4.5 Small Man Power Pivot Table
4.6 Small Man DashBoard

 

[Luke M]

Are "Chandoo", "PaulF", and "Luke M" in their own cells, or just the part of a longer sentence?

 

[Somendra Misra]

Hi Marreco,

Your counter is increasing with an irregular pattern. Is there any logic behind it?

Regards,

 

[marreco]

Hi Luke M, look my new file, please.

thank you

 

[Somendra Misra]

See this file, with helper cells (Yellow color).

Regards,

 

[Luke M]

If you insert a blank row before the start of your data, you could do this:
=IF(TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",999)),999))<>TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",999)),999)),INT(A1)+1.1,A1+0.1)

 

[Luke M]

Delete the blank row?

Assuming the blanks aren't in the middle of a "chapter"
=IF(B2="","",IF(TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",999)),999))<>TRIM(LEFT(SUBSTITUTE(B1," ",REPT(" ",999)),999)),INT(MAX(A$1:A1))+1.1,MAX(A$1:A1)+0.1))

 

[marreco]

Hi, very nice guys, so last question.

Is possible solve this detail?
look picture

 

[Luke M]

I'd first create a list with non-blanks (a quick search on these forums will give a formula solution to that, if needed), and then use the numbering formula.

 

[Faseeh]

Hi marreco

Please try this:

=SUMPRODUCT(1/COUNTIF($B$1:B1,"*"&LEFT($B$1:B1,FIND(" ",$B$1:B1))&"*"))&"."&COUNTIF($B$1:B1,"*"&LEFT(B1,FIND(" ",B1))&"*")

 

[marreco]

Hi Faseeh, thanks for reply, but result not espected.
1.1 chandoo VBA
1.1 chandoo Formul
1.1 chandoo Pivot
1.1 chandoo Chart
1.1 chandoo VBA
1.1 PaulF VBA
1.1 PaulF Formula
1.1 PaulF Chart
1.1 PaulF Power Pi
1.1 PaulF DashBoar
1.1 PaulF Solver
1.1 Luke M VBA
1.1 Luke M Formula
1.1 Luke M Pivot T
1.1 Small Man VBA
1.1 Small Man Form
1.1 Small Man Pivo
1.1 Small Man Char
1.1 Small Man Powe
1.1 Small Man Dash

 

[Faseeh]

@marreco,

Please see attachment

​

marreco,​

 

[marreco]

Very very good!!!!!!!!!!!!!!!!!

Thank you very much!!!!!!!!!!!!!!!