Auto-numbering

L

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

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 __
 
L

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
 
S

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.
 
L

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
 
S

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.
 
L

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
 
S

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

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
 
S

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto-Numbering fails 0
Sumproduct or ????? 5
Cell Counter/Increment 2
SUMPRODUCT 5
Another Sumif problem 1
Auto Fill a column 3
multiple dependent drop down lists 6
functions similar to filter 1

Top