[excel] I have filtered my Excel data and now I want to number the rows. How do I do that?

Basically all I want to do is to insert a new column after having filtered my data by a certain criterion, and then insert consecutive numbers into that column, one for each row. I.e., I have data like this in one column:

Armstrong, John

Beattie, Jane

Coombs, John

And I want a new column running next to it so it looks like:

1 Armstrong, John

2 Beattie, Jane

3 Coombs, John

I have tried inputting the first few numbers and then dragging down to fill the rest of the column but when I do that all of the numbers turn to 1 for some reason.

Try this:

On first row set value 1 (e.g cell A1)

on next row set: =A1+1

Finally autocomplete the remaining rows

I had the same need to fill up a column with a sequence series for each value on another column. I tried all the answers above and could not fix the problem. I solved it with a simple VBA macro.

My data have the same structure (but with 3000 rows):

• N2 is the column on which the table is filtered;
• N3 is the column where I wanted to fill a series;

A | B

N2 | N3

1 | 1

2 | 1

3 | 1

1 | 2

6 | 1

4 | 1

2 | 2

1 | 3

5 | 1

Here below the code:

> Sub Seq_N3() ' ' Seq_N3 Macro ' Sequence numbering of N3 based on N2 value
> do N2 
>     Dim N2 As Integer
>     Dim seq As Integer
> 
>         With ActiveSheet
>             
>             For N2 = 1 To 7 Step 1
>             seq = 1                 ' 
>             .Range("B2").Select     ' 
>             
>                 Do While ActiveCell.Offset(0, -1).Value2 <> 0
>                     
>                     If ActiveCell.Offset(0, -1).Value2 = N2 Then        
>                         ActiveCell.Value2 = seq                         
>                         seq = seq + 1                                   
>                         ActiveCell.Offset(1, 0).Select
>                     Else
>                         ActiveCell.Offset(1, 0).Select                  
>                     End If
>                     
>                 Loop
>                 
>             Next N2
>                    
>         End With End Sub

Hope it helps!

Try this function:

=SUBTOTAL(3, B$2:B2)

You can find more details in this blog entry.

Add a column for example 'Selected' First. Then Filter your data. Go to the column 'Selected'. Provide any proxy text or number to all rows. like '1' or 'A' - now your hidden Rows are Blank Now, Clear Filter and Use Sorting - two levels Sort by - 'Selected' Ascending - this leaves blank cells at bottom Add Sort Level - 'Any column you Desire' your order

Now, Why dont you drag the autofill yourself.

Oops, I have no reputation here.

I had the same problem. I'm no expert but this is the solution we used: Before you filter your data, first create a temporary column to populate your entire data set with your original sort order. Auto number the temporary "original sort order" column. Now filter your data. Copy and paste the filtered data into a new worksheet. This will move only the filtered data to the new sheet so that your row numbers will become consecutive. Now auto number your desired field. Go back to your original worksheet and delete the filtered rows. Copy and paste the newly numbered data from the secondary sheet onto the bottom of your original worksheet. Then clear your filter and sort the worksheet by the temporary "original sort order" column. This will put your newly numbered data back into its original order and you can then delete the temporary column.

Okay I found the correct answer to this issue here

Here are the steps:

1. Filter your data.
2. Select the cells you want to add the numbering to.
3. Press F5.
4. Select Special.
5. Choose "Visible Cells Only" and press OK.

6. Now in the top row of your filtered data (just below the header) enter the following code:

   =MAX($"Your Column Letter"$1:"Your Column Letter"$"The current row for the filter - 1") + 1

   Ex:

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

   Which would be applied starting at cell A27.

7. Hold Ctrl and press enter.

Note this only works in a range, not in a table!

Step 1: Highlight the entire column (not including the header) of the column you wish to populate

Step 2: (Using Kutools) On the Insert dropdown, click "Fill Custom List"

Step 3: Click Edit

Step 4: Create your list (For Ex: 1, 2)

Step 5: Choose your new custom list and then click "Fill Range"

DONE!!!

Easiest way do this is to remove filter, fill series from top of total data. Filter your desired data back in, copy list of numbers into a new sheet (this should be only the total lines you want to add numbering to) paste into column A1. Add "1" into column B1, right click and hold then drag down to end of numbers and choose "fill series". Now return to your list with filters and in the next column to the right "VLOOKUP" the filtered number against the list you pasted into a new sheet and return the 2nd value.