|
Auto-numbering
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 |
Auto-numbering
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 __ 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 |
Auto-numbering
On Jun 16, 11:26*am, "Héctor Miguel"
wrote: 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 __ 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 * 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- Hide quoted text - - Show quoted text - |
Auto-numbering
On Jun 15, 9:16 am, Len wrote:
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. |
Auto-numbering
On Jun 17, 12:42*am, Spiky wrote:
On Jun 15, 9:16 am, Len wrote: 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.- 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 |
Auto-numbering
On Jun 17, 10:04 pm, Len wrote:
On Jun 17, 12:42 am, Spiky wrote: On Jun 15, 9:16 am, Len wrote: 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.- 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 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. |
Auto-numbering
On Jun 18, 9:56*pm, Spiky wrote:
On Jun 17, 10:04 pm, Len wrote: On Jun 17, 12:42 am, Spiky wrote: On Jun 15, 9:16 am, Len wrote: 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.- 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 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 |
Auto-numbering
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? |
Auto-numbering
On Jun 19, 10:38*pm, Spiky wrote:
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 |
Auto-numbering
On Jun 19, 8:24 pm, Len wrote:
On Jun 19, 10:38 pm, Spiky wrote: 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 Try SUBTOTAL(3,$B$2:B2) in A2, copy down. |
Auto-numbering
On Jun 20, 10:36*pm, Spiky wrote:
On Jun 19, 8:24 pm, Len wrote: On Jun 19, 10:38 pm, Spiky wrote: 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 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 |
| All times are GMT +1. The time now is 12:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com