Auto-numbering

[Len]

Auto-numbering fails

After data filtering, it seems that auto-numbering in column A is not
working when I select range A2 to A13 and double click the Fill Handle
at cell A13 , e.g..


Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 SS
116 SS
119 WW
225 OO
230 DD
305 CC

In this case, how to solve the above scenario with Excel function and
give the result with auto-numbering as follows : -

Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 4 SS
116 5 SS
119 6 WW
225 7 OO
230 8 DD
305 9 CC

Please help, thanks

Regards
Len

 

[Héctor Miguel]

hi, Len !

Auto-numbering fails

After data filtering, it seems that auto-numbering in column A is not working
when I select range A2 to A13 and double click the Fill Handle at cell A13 , e.g.

instead of fill-handle for "auto-numbering" filtered lists...
-> try using subtotal(... worksheet function

hth,
hector.

__ OP __

 

[Len]

hi, Len !



instead of fill-handle for "auto-numbering" filtered lists...
-> try using subtotal(... worksheet function

hth,
hector.

__ OP __

Hi,

Thanks for your reply, I'm not quite understand how subtotal function
is worked in this scenario for auto-numbering, perhaps you can show me
how it work for the above scenario, thanks again

Regards
Len

 

[Spiky]

Auto-numbering fails

After data filtering, it seems that auto-numbering in column A is not
working when I select range A2 to A13 and double click the Fill Handle
at cell A13 , e.g..

Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 SS
116 SS
119 WW
225 OO
230 DD
305 CC

In this case, how to solve the above scenario with Excel function and
give the result with auto-numbering as follows : -

Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 4 SS
116 5 SS
119 6 WW
225 7 OO
230 8 DD
305 9 CC

Please help, thanks

Regards
Len

I'm a little confused by your data above, if you actually mean those
numbers as different rows or as data. If those are row numbers spread
from 2 to 305, a simple Fill may be impossible. But if you are really
just trying a simple Fill from cells A2 to A13, then....

Instead of the double-click method which requires Excel to guess what
you want, highlight just the first two entries (1 and 2 in your
example), and click-drag the fill handle down to where you want. This
should work better, as long as the numbering desired is simple like
1...2...3...4. Using the Fill Series... command in the Edit should
also work more consistently, although it's slower to use.

 

[Len]

I'm a little confused by your data above, if you actually mean those
numbers as different rows or as data. If those are row numbers spread
from 2 to 305, a simple Fill may be impossible. But if you are really
just trying a simple Fill from cells A2 to A13, then....

Instead of the double-click method which requires Excel to guess what
you want, highlight just the first two entries (1 and 2 in your
example), and click-drag the fill handle down to where you want. This
should work better, as long as the numbering desired is simple like
1...2...3...4. Using the Fill Series... command in the Edit should
also work more consistently, although it's slower to use.- Hide quoted text -

- Show quoted text -

Hi Spiky,

Thanks for your advice, I've tried =ROW ()-1, it gives wrong result
and copy down it becomes 1 all the way under column A and also, after
insert A2=1, A3=MAX(A$2:A2)+1, the result shows 2 and copy down it
becomes 2 all the way under column A.

Is my excel formula incorrect or have I missed out anything in this
formula or is there any excel function/formula more applicable ?

Regards
Len

 

[Spiky]

Hi Spiky,

Thanks for your advice, I've tried =ROW ()-1, it gives wrong result
and copy down it becomes 1 all the way under column A and also, after
insert A2=1, A3=MAX(A$2:A2)+1, the result shows 2 and copy down it
becomes 2 all the way under column A.

Is my excel formula incorrect or have I missed out anything in this
formula or is there any excel function/formula more applicable ?

Regards
Len

It sounds like you are trying to do something a little different, now.
I tried your formula and it worked for me, exactly as shown in your
post. Are you sure you did the copy correctly?

Although, all you really have to do in A3 is:
=A2+1

And copy down.

 

[Len]

It sounds like you are trying to do something a little different, now.
I tried your formula and it worked for me, exactly as shown in your
post. Are you sure you did the copy correctly?

Although, all you really have to do in A3 is:
=A2+1

And copy down.- Hide quoted text -

- Show quoted text -

Hi Spiky,

Op...! The formula MAX(A$2:A2)+1 was wrongly set at cell A3 and it
should be placed at cell A10 and yet it gives the result as 2 all the
way down when I copy down from A13 to A305.

Regards
Len

 

[Spiky]

I'm still not sure exactly what you are trying to do, esp since you
change it with each post. Are you trying to number by ones from A10 to
A305? So it would be numbered from 1 through 296?

 

[Len]

I'm still not sure exactly what you are trying to do, esp since you
change it with each post. Are you trying to number by ones from A10 to
A305? So it would be numbered from 1 through 296?

Hi Spiky,

Sorry for the confusion on the last posts and the ultimate result
should give auto-numbering for filtered rows as follows based on my
1st post above : -

Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 4 SS
116 5 SS
119 6 WW
225 7 OO
230 8 DD
305 9 CC


After several attempts to explore other excel formulas/functions on
how to solve the auto-numbering after data filtered, I begin to learn
to use "MAX" excel function and when I inset A2=1 and A10=MAX(A
$2:A2)+1 and it gives the value as 2, then copy down from A13 to A305
( ie visible cells only ) and yet it gives the result as 2 starting
from A10 to A305 . Thus, the above excel formulas is incorrect and how
to formulate it to obtain the result same as shown above table

Regards
Len

 

[Spiky]

Hi Spiky,

Sorry for the confusion on the last posts and the ultimate result
should give auto-numbering for filtered rows as follows based on my
1st post above : -

Column A B
Row
2 1 AA
10 2 BB
13 3 CC
39 4 SS
116 5 SS
119 6 WW
225 7 OO
230 8 DD
305 9 CC

After several attempts to explore other excel formulas/functions on
how to solve the auto-numbering after data filtered, I begin to learn
to use "MAX" excel function and when I inset A2=1 and A10=MAX(A
$2:A2)+1 and it gives the value as 2, then copy down from A13 to A305
( ie visible cells only ) and yet it gives the result as 2 starting
from A10 to A305 . Thus, the above excel formulas is incorrect and how
to formulate it to obtain the result same as shown above table

Regards
Len

Try SUBTOTAL(3,$B$2:B2) in A2, copy down.

 

[Len]

Try SUBTOTAL(3,$B$2:B2) in A2, copy down.- Hide quoted text -

- Show quoted text -

Hi Spiky,

Thanks for your help and finally it works.


Cheers
Len