Advanced filter does not equal to a list of IDs from another workbook.

[Harshil Mehta]

Hi, I have a list of IDs from range A1, A2 and so on (Including Header in range A1) which I want the code to exclude while filtering the data from another workbook.

My criteria range is B25:G26. I tried using <>*ID* by the pasting this in Range B26 (B25 as the header), which ran fine just for a single ID but does not work for the entire list.

What formula should I type in Range B26 so that the code understands that it should exclude the IDs in rangeA1, A2 and so on?

Could anyone please help me solve this issue?

 

[pgc01]

Hi

Not sure I understood exactly what you need but try this example.

The IDs to exclude are in column A

I used in the Advanced Filter

List Range: $C$1:$C$10
Criteria range: $E$1:$E$2
Copy to: $G$1

In the criteria range I used a calculated field. In E2:

=(SUMPRODUCT(--ISNUMBER(SEARCH($A$2:$A$5,C2)))=0)

In column G I got the ID's in column C that do not contain any of the IDs in column A.

Book1
	A	B	C	D	E	F	G	H
1	ID		ID_List		Exclude_IDs		ID_List
2	AA		12AA3		False		13AB4
3	BB		13AB4				BD24
4	CC		14BB				12AC34
5	DD		CC16				AB34
6			BD24
7			12AC34
8			DD
9			AB34
10			1133AA
11
Sheet6

 

[Harshil Mehta]

Thank you for your help!

 

[pgc01]

You're welcome

 

[Harshil Mehta]

You're welcome

Hi, I have posted another query on advanced filter, could you please try to help me?