I have filtered my Excel data and now I want the row count. How should I do it?
Basically all I want to do is insert a new column after filtering my data by a specific criterion and then insert consecutive numbers into that column, one for each row. Ie, I have data like this in one column:
Armstrong, John
Beatty, Jane
Coombs, John
And I want a new column next to it that looks like this:
1 Armstrong, John
2 Beatty, Jane
3 Coombs, John
I tried to enter the first few numbers and then drag and drop them to fill the rest of the column, but when I do that, all the numbers turn to 1 for some reason.
Thanks for any help.
source to share
Ok I found the correct answer to this question here
Here are the steps:
- Filter your data.
- Select the cells you want to add numbering to.
- Press F5.
- Select Special.
- Select Visible Cells Only and click OK.
-
Now in the top row of the filtered data (just below the header) enter the following code:
= MAX ($ "Your column letter" $ 1: "Your column letter" $ "The current line for the filter is 1") + 1
Example:
= MAX ($ A $ 1: A26) + 1
to be applied starting at cell A27.
-
Hold Ctrl and press enter.
Note that this only works on a range, not a table!
source to share
Add a column like 'Selected' First. Then filter the data . Go to the "Selected" column. Provide any text or proxy number for all lines . eg "1" or "A" - now your hidden rows are empty Now Clear filter and use Sort - two levels Sort by - "Selected" Ascending - this leaves blank cells at the bottom Add sort level - "Any column you want" your order
Now why do n't you drag the autocomplete yourself.
Oops, I have no reputation here.
source to share
I had the same need to fill a column with a series of sequences for each value in a different column. I've tried all the answers above and couldn't solve the problem. I solved it with a simple VBA macro.
My data has the same structure (but with 3000 lines):
- N2 is the column on which the table is filtered;
- N3 is the column where I wanted to fill the series;
A | IN
N2 | N 3
1 | 1
2 | 1
3 | 1
1 | 2
6 | 1
4 | 1
2 | 2
1 | 3
5 | 1
Below is 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!
source to share
Step 1: select the entire column (not including the header) of the column you want to fill
Step 2: (Using Kutools) In the "Insert" dropdown list, click "Fill Custom List"
Step 3: Click "Change"
Step 4: Create your list (for Ex: 1, 2)
Step 5. Select a new custom list and click "Fill Range"
DONE !!!
source to share
The easiest way is to remove the filter, fill the rows at the top. Filter the data you want, copy the list of numbers to a new sheet (these are just general lines that you want to add numbering) and paste into column A1. Add "1" to column B1, right-click and hold, then drag down to the end of the number and select "fill series". Now go back to your list with filters and in the next column to the right "VLOOKUP" the filtered number is mapped to the list you pasted into the new sheet and return the second value.
source to share