Assigning numbers to selected rows

%3CLINGO-SUB%20id%3D%22lingo-sub-2253140%22%20slang%3D%22en-US%22%3EAssigning%20numbers%20to%20selected%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253140%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20file%20with%20about%20400%20rows%20which%20I%20have%20set%20up%20to%20allow%20me%20to%20select%20using%20filters%20on%20any%20of%20the%20columns.%26nbsp%3B%20I%20need%20to%20assign%20sequential%20numbers%2C%20starting%20with%20%221%22%2C%26nbsp%3B%20to%20the%20selected%20rows.%26nbsp%3B%20I%20can't%20figure%20out%20how%20to%20number%20ONLY%20the%20selected%20rows%20without%20human%20intervention.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%2C%20if%20my%20filter%20selects%20the%2040th%20through%20the%2060th%20rows%20of%20my%20file%2C%20how%20can%20I%20number%20only%20those%20rows%2C%20starting%20with%20%221%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2253140%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2253171%22%20slang%3D%22en-US%22%3ERe%3A%20Assigning%20numbers%20to%20selected%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F753488%22%20target%3D%22_blank%22%3E%40NorskGrandpa%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20that%20you%20want%20the%20sequence%20numbers%20in%20column%20A.%3C%2FP%3E%0A%3CP%3ESelect%20the%20visible%20(filtered)%20cells%20in%20column%20A.%3C%2FP%3E%0A%3CP%3EIn%20the%20following%2C%20I%20will%20assume%20that%20the%20first%20cell%20in%20the%20selection%20is%20A40.%3C%2FP%3E%0A%3CP%3EEnter%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMAX(%24A%241%3A%24A39)%2B1%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ewhere%2039%20is%20the%20row%20number%20of%20the%20cell%20above%20the%20first%20cell%20in%20the%20selection.%3C%2FP%3E%0A%3CP%3EConfirm%20the%20formula%20with%20Ctrl%2BEnter%20so%20that%20it%20is%20entered%20in%20all%20selected%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2253187%22%20slang%3D%22en-US%22%3ERe%3A%20Assigning%20numbers%20to%20selected%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2253187%22%20slang%3D%22en-US%22%3EUnless%20I%20misunderstand%2C%20this%20appears%20to%20require%20human%20intervention%20to%20enter%20the%20address%20of%20the%20cell%20before%20the%20selected%20cell.%20I%20would%20like%20to%20programmatically%20identify%20the%20first%20selected%20row%20and%20start%20numbering%20there%20--%20in%20advance%20I%20would%20not%20know%20which%20row%20would%20be%20the%20first%20selected.%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a file with about 400 rows which I have set up to allow me to select using filters on any of the columns.  I need to assign sequential numbers, starting with "1",  to the selected rows.  I can't figure out how to number ONLY the selected rows without human intervention. 

 

In other words, if my filter selects the 40th through the 60th rows of my file, how can I number only those rows, starting with "1"?

4 Replies

@NorskGrandpa 

Let's say that you want the sequence numbers in column A.

Select the visible (filtered) cells in column A.

In the following, I will assume that the first cell in the selection is A40.

Enter the formula

 

=MAX($A$1:$A39)+1

 

where 39 is the row number of the cell above the first cell in the selection.

Confirm the formula with Ctrl+Enter so that it is entered in all selected cells.

Unless I misunderstand, this appears to require human intervention to enter the address of the cell before the selected cell. I would like to programmatically identify the first selected row and start numbering there -- in advance I would not know which row would be the first selected.

@NorskGrandpa 

Try this:

=SUM(A1,SUBTOTAL(3,B2))

Row 1 contains the headers. The formula starts in A2.

 

I didn't describe the problem adequately, however the responses I received got me thinking of a solution which worked. Thanks for jogging my thought process out of the rut it was in>