Autofilter help

[shanep]

Hi

I'm having a little trouble with VBA and AutoFilter...

I have a column full of hostnames. I have done a lookup on these hostnames in another worksheet and have changed the cell.interior.color to RGB(255,0,0) for any hosts that are not in the other worksheet.

I now need to count the number of UNIQUE hostnames that are highlighted red.

I have worked out (with the help of this board ) how to count the red instances using Autofilter, but I cannot workout how to then further filter that column on unique instances.

e.g. my column of hostnames loooks like this post lookup (where host2 and host5 are red because they do not exist in the other worksheet):

Column A
host1
host2
host2
host2
host3
host4
host4
host4
host5
host5
host6
host7

from the above list I would want to count 2, i.e one instance of:

host2
host5

Can anyone help?

Thanks
Shane

 

[shanep]

little nudge...

 

[Weaver]

Rather than changing cell colours to red, I would suggest you have some sort of indicator, maybe a Y/N or TRUE/FALSE in another column, as unless you're on 2007, colours are awkward to count, total filter etc. I say this to my team so much they all take the mickey, but nobody uses colours to indicate anything important any more.

If you need the colour as well, you can always conditionally format the row based on your indicator column.

As for filtering on unique instances, I usually add a column and drop a formula in it comparing the value of column A in the current row against the value in the row above. So if they're equal, the current instance must be a duplicate. Obviously, this only works if they've been filtered. Then you might try this:

Say your data begins on row 2 to allow for headers. Stick another column in and use the following formula

=countif(A2:$A$2,A2) in cell B2 (or whatever) then copy down to the bottom of your range. Where this results in a 1, this will be the first instance of the given value, so if you filter on 1's you get your unique results filter.

Hope that helps.

 

[shanep]

Hi excelR8R, and thanks for the response.

I notice your signature quote and (truth be told) I'd prefer to do this with a cell formula but unfortunately this is only a small part of a much larger piece of code so I do need to do it with VB.

Fortunately though, I am on 2007 and I will be the only person ever running this code so I can filter on cell colour as opposed to having another column with an flag in it.

like i said, i've got the code for the initial filter (based on color) but i can't work out how to then filter the same column again based on unique values. do you know a way of coding this?

Thanks
Shane