Autonumber a filtered table

**wefrucar** · 02-23-2009, 08:09 AM

Hi,
As the title says, I'd like to autonumber a filtered table.

For example, take this table:

a 1
b 3
c 8
d 0
e 5
f 1

Now, let's say I filter this table for values greater than 2. I'd like to have the numbers "1, 2, 3" come up next to b, c, e, respectively. The quickest and least tedious way possible.
And then maybe I want to re-filter so that only values of exactly 1 come up. Then "1, 2" should come up next to a and f respectively.

The only way I can think of is to add a column that is not part of the table and make that numbered. The problem with that is that then it will not follow the formatting of the table. I could, of course, copy formatting every time I change the numbering, but that's a little tedious, as I may need to re-filter the list often.... and the table might get long.

Thanks for your help!

**Cheeky Charlie** · 02-23-2009, 08:23 AM

You will need an additional column for the count though...

Assuming the table you outlined is at A2, how about this:
C2=SUBTOTAL(103,B$2:B2)
copied down

HTH

**martindwilson** · 02-23-2009, 08:49 AM

cc
is it different in 2007 ?
mine would be
SUBTOTAL(3,B$2:B2)

**Cheeky Charlie** · 02-23-2009, 09:03 AM

Crikey, a chance for me to teach the great MartinD something! Or perhaps you're just humouring me...

<from help>
SUBTOTAL(function_num, ref1, ref2, ...)

Function_num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list.

By specifying function+100 (in this case COUNTA) I exclude the rows which are hidden when the OP applies a filter - hence a numbered list consecutive from 1, regardless of filters applied.

Subtotals are normally created when you summarise a sheet with the subtotals tool in Excel (Data->subtotals). Very rarely have I seen someone who has both : a) organised their data well enough for this to work effectively and b) not already capable of summarising their data in more flexible and purpose-built ways. Because they were designed this way around, though, their use is a little more obscure than, say, vlookup.

CC

**martindwilson** · 02-23-2009, 09:20 AM

well its just i get #value with =SUBTOTAL(103,B$2:B2) but not with
=SUBTOTAL(3,B$2:B2) thats why i wonder if its 2007
see attached
curently filtered custom>2 on c

**Cheeky Charlie** · 02-23-2009, 09:57 AM

This is strange... has the function of subtotal changed in some way?

I get exactly the same results for 3 and 103 (and a couple of others I've tried like SUM - 9).

I'm sure it didn't work this way before, I remember using this feature specifically to summarise filtered data. Perhaps this was XLXP?

It seems that there was once a difference between these functions (evidence entirely anecdotal, apparently) then there was no difference (~XL03) and now there is no 103 etc. (XL07) - which would make sense if it did exactly the same as 3 etc.

<EDIT>

I think I'm just going mental - a quick check has shown that the only difference is caused by actually hiding (not filtering) rows. I think I've caused enough trouble for today...

In sum, either should work (although not, apparently in XL07).

Why subtotal 103 doesn't work for Matrin is an altogether different issue (it does work for me on the posted attachment).