Formula to add unique values from range EXCEPT values in another range

[kirbyfez]

Hi guys,

I have no idea if this is possible but I hope so.

We have a document here at work where we log all our transactions and in the summary field we have lots of formulas based on customers which is all row based.

Issue is that we have certain codes that we do not want to include in the summary sheet and want these not to be added.

For example.

In Sheet called Monday there are customer names in B2:B301. I want the unique values from that range to be listed in B5:B29 in a sheet called summary. but would want it to not add all strings listed in range Z1:Z50 in sheet Admin...

This was very crappy explained but hopefully you get the drift.

At the moment I have a macro that can do it, but it's so slow and wouldnt work in the long run. Also have a formula that takes it over but doesnt exclude the ones that shouldnt be added.

Current formula is: =IF(ROWS(B$5:B5)>$B$1,"",(INDEX(Table1[CUSTOMER/STATUS],MATCH(ROWS(B$5:B5),Table1[Num],0))))
which is then dragged down from B5 to B29

Anyone that can offer some assistance here?