use =FILETER to exclude values in other ranges

[smking204]

I have a master list called StockCode on a tab called InvMaster. On a separate tab, I want to recreate the list in column B such that it will exclude any value found in columns A, C, or D.

I tried this formula, testing it only with column A, but it returned #VALUE!:

=FILTER(InvMaster[StockCode], (InvMaster[StockCode]<>A:A))

The only way I can get it to do anything is to replace A:A with a single cell (i.e., A12). How do I get it to filter out values from these three other ranges? Is =FILTER the best function to use? Thanks in advance for the help!

 

[Fluff]

Can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[smking204]

Can you post a sample of your data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I don't think my work computer will let me, but the concept is pretty straightforward. I'll try to illustrate with this table. In this case, the filter in column B should return a list of 2, 4, 5, 6, and 7. But column F happens to be on a separate tab. When I try to recreate this oversimplified version on one tab in excel, I get a different error, #SPILL!, which doesn't make any sense to me because there's nothing obstructing the spill... Anyway, hope this is enough to work with. Thanks.

A​	B​	C​	D​	E​	F​
1	=FILTER(F:F, F:F<>A:A)				1
3					2
8					3
					4
					5
					6
					7
					8

 

[Fluff]

Ok, how about

=FILTER(F1:F1000,(F1:F1000<>"")*(ISNA(MATCH(F1:F1000,A1:A10,0))))

 

[smking204]

hmmm that's a step closer, for sure. But how can I exclude items from A, C, and D, rather than just A? I've tried a few things, like using OR in a few places. No luck so far...

 

[Fluff]

How about

=FILTER(F1:F1000,(F1:F1000<>"")*(ISNA(MATCH(F1:F1000,A1:A10,0)))*(ISNA(MATCH(F1:F1000,C1:C10,0)))*(ISNA(MATCH(F1:F1000,D1:D10,0))))

 

[smking204]

Ok, I think that's the ticket. I did try that earlier, but I think there were formatting issues that were tripping it up. I tried using AND's and OR's, because I was getting confused Thanks a million!

While we're on the subject, I'll ask for a little more clarification: it seems like I can use * in place of AND() in some cases (but in other cases it still performs multiplication). Is there something similar to replace OR()? And feel free to correct any misunderstanding I'm showing. I had little luck googling these things.

 

[Fluff]

As that is an array calculation you need to use the array form of AND * alternatively for OR it's +