I'm stumped by Advanced Filter Criteria not equal to syntax

[logandiana]

Please help!
In my range data I have data:
Column A like red, blue, green, etc.
Column B has data like In Stock Warehouse A, In Stock Warehouse B, Out of Stock, etc.
Column C has account numbers stored as text like '12345, '12346, '12347, etc.

For my range criteria I have:

column A red
column B in stock?
column C <>12346

For the result:
I am getting everything red and in stock as expected, but the criteria in column C is not working and is returning all account numbers
I've tried
<>12346
<>'12346
="<>12346"
="<>'12346"
="<>"&"12346"
="<>"&"'12346"
I either get back no results or results that DO include account 12346

In my criteria for column C if I use just 12346 (to include it) then I do indeed get the 1 record back in the results.
But I can't figure out the proper syntax to exclude a number formatted as text.


Thanks

 

[Marcelo Branco]

Use a formula as criteria. This worked for me

Data including headers in A6:C13
Criteria Range = A1:C2
Observe that cell C1 must be empty or contain something other than field names (i used Formula)
Formula in C2
=C7<>"12346"
(VERDADEIRO in Portuguese = TRUE in English)

Before filter

Pasta1
	A	B	C
1	Color	Stock	Formula
2	Red	In Stock	VERDADEIRO
3
4
5
6	Color	Stock	Account
7	Red	In Stock Warehouse A	12345
8	Blue	Out of stock	12347
9	Red	In Stock Warehouse B	12346
10	Red	In Stock Warehouse A	12346
11	Red	In Stock Warehouse C	12348
12	Green	Out of stock	12349
13	Red	Out of stock	123410
Plan2

After Advanced Filter

Pasta1
	A	B	C
1	Color	Stock	Formula
2	Red	In Stock	VERDADEIRO
3
4
5
6	Color	Stock	Account
7	Red	In Stock Warehouse A	12345
11	Red	In Stock Warehouse C	12348
14
Plan2

Hope this helps

M.

 

[logandiana]

This isn't working for me.
I wouldn't mention that my range data is in a separate workbook and my account column does not appear as a column in my output (I want to filter them out, but not display them)
I don't know if this matters or not, but the method above is not working for me.
It's still pulling in results which contain 12346
Does it have anything to do with the formatting of the cells or that the number is formatted as text.?
Still stumped

 

[Marcelo Branco]

Are the data on a different Workbook? Or on a different Worksheet but in the same Workbook?
I'm not understanding what you need...
Maybe someone else can help you..

M.

 

[logandiana]

Nevermind, I got it working. When referencing the other sheet it picked it up as $N$2. Took a bit, but figured out to remove the second $ to make $N2. Works well now.

 

[Marcelo Branco]

Glad it's working now!

M.