Excel Advanced Filter Query

  Ben Avery 11:44 03 Nov 03
Locked

G'day

I have a quick question regarding an advanced filter on excel.

I have managed to set a filter which picks out the words in a column which I want using an advanced filter by typing the words into different rows in the criteria range. This works fine, but how do I change it to EXCLUDE these words?


**************************************************


Criteria range example:

Cell A1 = Forename Cell A2 = Surname

Cell B1 = BLANK> Cell B2 = Smith

Cell C1 = BLANK> Cell C2 = Jones


**************************************************

This sorts out all of the Smith & Jones' (no pun intended!) but how do I run the query excluding Smith & Jones' from the selection?

BA

  graham√ 12:32 03 Nov 03

The auto filter will do that. In the drop down menu select Custom and enter the parameters.

  Ben Avery 12:41 03 Nov 03

Autofilter only performs one operation at a time in each column doesn't it? How can you set autofilter to do what I need?

BA

  Ben Avery 12:45 03 Nov 03

I meant it only performs 2 operations at a time. What would be the equivilent formula to type into the Advanced filter to perform the operations the custom filter will do for you?

BA

  VoG II 12:51 03 Nov 03

Can't you use

Cell B2 <> Smith

  Ben Avery 13:07 03 Nov 03

Doesn't seem to work. The above was an example only, it's too much to go into what is actually on the sheet I'm doing but basically I have the advanced filter set up how you showed me quite some time ago.

That works really well.

I can use the advanced filter to search for several names at once by typing them in the same criteria range column but on seperate rows and adjusting the criteria range accordingly. There is no limit to how many names I can search for this way, but I can only only seem to search for names which I want to KEEP IN the filter not names I want to EXTRACT FROM the filter.

That's where my query lies.

If I have a list of surnames in column B, what do I type in the criteria range cells (say cells B2, B3, B4 & B5) to EXCLUDE "Smith", "Jones", "Brown" and "McDonald"?

By typing the words in on their own, I can remove the other names but I want to do the opposite without having to typ all the other words in the list in.

Am I making sense? Or is this dull, confusing and frankly less interesting than an in-flight magazine produced by Air Belgium??? ;o)


BA

  VoG II 17:34 03 Nov 03

I think that you need to change the criteria range cells to

<> "Smith" etc.

I understand what you want to do but not how, never having done this myself.

You could also use a little VBA routine to achieve what you want. The following code should be placed in a module.

It assumes that you have entered the names that you want excluded from your filter in Row 1 from Column A across. It further assumes that your names are in a list in Column A starting from Row 2.

Amend as required to achieve your objective

------------------------------------------

Sub HideNames()

Dim iCol As Byte

Dim iRow As Integer


iRow = 2

Do Until Cells(iRow, 1) = ""

iCol = 1

Do Until Cells(1, iCol) = ""

If Cells(iRow, 1) = Cells(1, iCol) Then

Rows(iRow).Select

Selection.EntireRow.Hidden = True

Exit Do

End If

iCol = iCol + 1

Loop

iRow = iRow + 1

Loop

End Sub

--------------------------------

HTH

  VoG II 23:13 03 Nov 03

Very neat!

Just to be a pedant, you could find the last row using

xRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

  powerless 23:16 03 Nov 03

Somebody has met his match.

  VoG II 23:21 03 Nov 03

I didn't realise it was a competition.

Any regular knows that Whisperer is better than me at Excel but - as his name suggests - he only appears when I run out of ideas.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…