Auto renumber column after filtering?
I currently have column A as a numbered column. As the title suggests, is there a way to auto renumber this column after filtering so that it will always be sequential starting with 1.
Question marked as
Top-ranking reply
I'm not coming up with anything. I can't think of a way to determine if a row is showing vs filtered when the built-in filters are used to do it. Maybe someone else will think of something and it will negate all the stuff I am going to write below.
There is a way to "filter" that uses a table to set the parameters and a column (or several) to determine which rows should be showing. You might like this idea better anyway because it makes it easier to set the filter parameters if you will be changing them often. It is kind of hard to set it up but is handy to use.
Below is an example that uses 3 new columns (to make it easier to understand what is going on). It could be done in one column and I think could be done in a completely separate table to keep it out of your data table. A filter would be made for column F to show only those rows that are TRUE. I suggest using the "cell is TRUE" filter versus using a quick filter. In another thread we found the "quick" filters to be anything but quick.
Before turning on the filter:
With the filter on:
You would of course hide the "filter" columns. This whole thing can be cleaned up a lot more. I think the "filter" columns could be placed in a separate table on a separate sheet out of the way and the filter could be based on column A (show rows where column A >0).
I'm not suggesting you use this as it is but here are the formulas in this example
Table 1
A2 =IF(F2=TRUE,COUNTIF(OFFSET(F$1,0,0,ROW(),1),TRUE),"")
D2 =LEFT(B2,LEN(Table 2::$C$2))=Table 2::$C$2
E2 =AND(C2≥Table 2::$C$3,C2≤Table 2::$C$4)
F2 =COUNTIF(D2:E2,FALSE)=0
Fill down with all to complete the columns
Table 2
C2 =B2&""
C3 =IF(B3="",0,B3)
C4 =IF(B4="",9999,B4)