I have filtered my Excel data and now I want to number the rows How do I do that

Question

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

User · Answer

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

User · Answer

Try this   On first row set value 1  e g cell A1   on next row set   A1 1  Finally autocomplete the remaining rows

User · Answer

Okay I found the correct answer to this issue here  Here are the steps    Filter your data  Select the cells you want to add the numbering to  Press F5  Select Special  Choose  Visible Cells Only  and press OK  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  Hold Ctrl and press enter    Note this only works in a range  not in a table

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    gt  Sub Seq N3       Seq N3 Macro   Sequence numbering of N3 based on N2 value  gt  do N2   gt      Dim N2 As Integer  gt      Dim seq As Integer  gt    gt          With ActiveSheet  gt                gt              For N2   1 To 7 Step 1  gt              seq   1                     gt               Range  B2   Select         gt                gt                  Do While ActiveCell Offset 0  -1  Value2  lt  gt  0  gt                        gt                      If ActiveCell Offset 0  -1  Value2   N2 Then          gt                          ActiveCell Value2   seq                           gt                          seq   seq   1                                     gt                          ActiveCell Offset 1  0  Select  gt                      Else  gt                          ActiveCell Offset 1  0  Select                    gt                      End If  gt                        gt                  Loop  gt                    gt              Next N2  gt                       gt          End With End Sub   Hope it helps

User · Answer

Try this function    SUBTOTAL 3  B 2 B2    You can find more details in this blog entry

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

Examples related to excel

Examples related to filtering