Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to number rows after data has been filtered?
I want to know continously give a serial number skipping in between some rows
that are filtered. In excel if we try Fill series, it is not working on filtered rows. Is there any way out? Please help. |
#2
|
|||
|
|||
I think I'd sort the data to group those visible cells. Then fill those empty
cells and resort the data to its original order. I'd have a column that was essentially a row number column. show all the rows first. Insert a new column A. put 1 in A2 and 2 in A3 select a2:a3 and drag down your data. Filter your data to see the blanks you want to fill. select that range of visible cells and type in XXXXXX, but hit ctrl-enter to fill all those selected cells. Now sort by that column. filter to show just the XXXXXX's. All the XXXXXX's will be grouped together and you can fill those cells with what you want. Then show all the rows and sort by that column A to put things in their original order. Maria wrote: I want to know continously give a serial number skipping in between some rows that are filtered. In excel if we try Fill series, it is not working on filtered rows. Is there any way out? Please help. -- Dave Peterson |
#3
|
|||
|
|||
If you want to know the record number, in relation to the visible
records, you could use the following formula, and copy it down to the end of the list: =SUBTOTAL(3,$D$2:D2) where the first row of data in the unfiltered table is row 2, and there is an entry in every row in column D. Note: Don't use this technique if you plan to use Excel's Subtotal feature (DataSubtotals) -- it may delete your table when you remove the Subtotals. Maria wrote: I want to know continously give a serial number skipping in between some rows that are filtered. In excel if we try Fill series, it is not working on filtered rows. Is there any way out? Please help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
Just to add to Debra's reply. If you filter by a different criteria (or show
all), your numbers will change since it is a formula. Debra Dalgleish wrote: If you want to know the record number, in relation to the visible records, you could use the following formula, and copy it down to the end of the list: =SUBTOTAL(3,$D$2:D2) where the first row of data in the unfiltered table is row 2, and there is an entry in every row in column D. Note: Don't use this technique if you plan to use Excel's Subtotal feature (DataSubtotals) -- it may delete your table when you remove the Subtotals. Maria wrote: I want to know continously give a serial number skipping in between some rows that are filtered. In excel if we try Fill series, it is not working on filtered rows. Is there any way out? Please help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign a text value to a variable number of rows? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) | |||
Insert rows | Excel Worksheet Functions | |||
Is there a limit to the number of rows that can be filtered | Excel Worksheet Functions | |||
Multiple worksheet queries | Excel Worksheet Functions |