Advanced Filter with array

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Advanced Filter with array

Post by agibsonsw »

Hello. (2003)
Is it possible to use an array formula as a calculated criteria for Advanced Filter? I've array entered this formula as a criteria:
=SUM(IF(VLOOKUP($A6,staff3,COLUMN(B6:J6),0)<>B6:J6,1,0))
'staff3' is a table on a separate worksheet.
The formula returns TRUE if a value in column B to J differs to the corresponding value in table 'staff3', and it works if I drag it down a column.

(This follows on from a recent question I've asked, but I now just need to know if the Advanced Filter can understand an array formula.)
Thanks, Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Advanced Filter with array

Post by HansV »

Your formula doesn't return TRUE or FALSE, it returns the number of differences. If you enter a column heading (field name) above the formulas and use the same column heading in the criteria range, you can filter for a specific number of differences, for example 0 if you want to see only the records with no differences, or >0 if you want to see only the records with differences.
But since the formula doesn't return TRUE or FALSE, you can't use it in a calculated condition in the Criteria range without a column heading.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Advanced Filter with array

Post by agibsonsw »

Thank you. I thought that any numerical value other than zero was 'true'? Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.

User avatar
HansV
Administrator
Posts: 78370
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Advanced Filter with array

Post by HansV »

That's true, but it doesn't work for calculated criteria as far as I can tell, I think you'll need to add a calculated column to your table.
Best wishes,
Hans

User avatar
agibsonsw
SilverLounger
Posts: 2403
Joined: 05 Feb 2010, 22:21
Location: London ENGLAND

Re: Advanced Filter with array

Post by agibsonsw »

Hi.
I think you're right. I tried wrapping an IF function to return a value of TRUE or FALSE, But I'm getting the impression that Advanced Filter cannot handle an
array formula. Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.