Filter function that must exclude specific data

Brentsa

Board Regular
Joined
Oct 3, 2013
Messages
118
Office Version
  1. 365
Platform
  1. Windows
I'm using Filter function. lets say my table is as follows:

NameHexRedGreenBlueHueSatur.LightSatur.Value
(RGB)(RGB)(RGB)(RGB)(HSL/HSV)(HSL)(HSL)(HSV)(HSV)
Absolute Zero#0048BA0%28%73%217°100%37%100%73%
Acid green#B0BF1A69%75%10%65°76%43%76%43%
Aero#7CB9E849%73%91%206°70%70%47%91%
Aero blue#C0E8D575%91%84%151.5°47%83%17.20%91%
African violet#B284BE70%52%75%288°31%63%31%75%
Air superiority blue#72A0C145%63%76%205°39%60%41%76%
Alabaster#EDEAE093%92%88%50°27%90%6%93%
Alice blue#F0F8FF94%97%100%208°100%97%6%100%
Alloy orange#C4621077%38%6%27°85%42%92%77%
Almond#EFDECD94%87%80%30°52%87%14%94%
Amaranth#E52B5090%17%31%348°78%53%81%90%
Amaranth (M&P)#9F2B6862%17%41%328°57%40%73%62%
Amaranth pink#F19CBB95%61%73%338°75%78%35%95%
Amaranth purple#AB274F67%15%31%342°63%41%77%67%
Amaranth red#D3212D83%13%18%356°73%48%84%83%
Amazon#3B7A5723%48%34%147°35%36%52%48%
Amber#FFBF00100%75%0%45°100%50%100%100%
Amber (SAE/ECE)#FF7E00100%49%0%30°100%50%100%100%
Amethyst#9966CC60%40%80%270°50%60%50%80%
Android green#A4C63964%78%22%74°55%50%71%78%
Antique brass#CD957580%58%46%22°47%63%43%80%
Antique bronze#665D1E40%36%12%53°55%26%71%40%
Antique fuchsia#915C8357%36%51%316°22%46%37%57%
Antique ruby#841B2D52%11%18%350°66%31%80%52%
Antique white#FAEBD798%92%84%34°78%91%14%98%
Ao (English)#0080000%50%0%120°100%25%100%50%
Apple green#8DB60055%71%0%74°100%36%100%71%
Apricot#FBCEB198%81%69%24°90%84%29%98%
Aqua#00FFFF0%100%100%180°100%50%100%100%
Aquamarine#7FFFD450%100%83%160°100%75%50%100%
Arctic lime#D0FF1482%100%8%72°100%54%92%100%
Army green#4B532029%33%13%69°44%23%61%33%
Artichoke#8F977956%59%47%76°13%53%20%59%
Arylide yellow#E9D66B91%84%42%51°74%67%54%91%
Ash gray#B2BEB570%75%71%135°8%72%6%75%
Asparagus#87A96B53%66%42%93°26%54%37%66%
Atomic tangerine#FF9966100%60%40%20°100%70%60%100%
Auburn#A52A2A65%16%16%59%41%75%65%
Aureolin#FDEE0099%93%0%56°100%50%100%99%
Avocado#56820334%51%1%81°95%26%98%51%
Azure#007FFF0%50%100%210°100%50%100%100%
Azure (X11/web color)#F0FFFF94%100%100%180°100%97%6%100%

I want to filter this list but I do not want the following to appear in my data:
Antique bronze
Avocado
Absolute Zero
Amber
Amethyst

How can I still use the filter function?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
See if this would suit you.

20 10 13.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1NameHexRedGreenBlueHueSatur.LightSatur.Value
2(RGB)(RGB)(RGB)(RGB)(HSL/HSV)(HSL)(HSL)(HSV)(HSV)Exclude
3Absolute Zero#0048BA0%28%73%217°100%37%100%73%Antique bronzeAcid green#B0BF1A69%75%10%65°76%43%76%43%
4Acid green#B0BF1A69%75%10%65°76%43%76%43%AvocadoAero#7CB9E849%73%91%206°70%70%47%91%
5Aero#7CB9E849%73%91%206°70%70%47%91%Absolute ZeroAero blue#C0E8D575%91%84%151.5°47%83%17%91%
6Aero blue#C0E8D575%91%84%151.5°47%83%17.20%91%AmberAfrican violet#B284BE70%52%75%288°31%63%31%75%
7African violet#B284BE70%52%75%288°31%63%31%75%AmethystAir superiority blue#72A0C145%63%76%205°39%60%41%76%
8Air superiority blue#72A0C145%63%76%205°39%60%41%76%Alabaster#EDEAE093%92%88%50°27%90%6%93%
9Alabaster#EDEAE093%92%88%50°27%90%6%93%Alice blue#F0F8FF94%97%100%208°100%97%6%100%
10Alice blue#F0F8FF94%97%100%208°100%97%6%100%Alloy orange#C4621077%38%6%27°85%42%92%77%
11Alloy orange#C4621077%38%6%27°85%42%92%77%Almond#EFDECD94%87%80%30°52%87%14%94%
12Almond#EFDECD94%87%80%30°52%87%14%94%Amaranth#E52B5090%17%31%348°78%53%81%90%
13Amaranth#E52B5090%17%31%348°78%53%81%90%Amaranth (M&P)#9F2B6862%17%41%328°57%40%73%62%
14Amaranth (M&P)#9F2B6862%17%41%328°57%40%73%62%Amaranth pink#F19CBB95%61%73%338°75%78%35%95%
15Amaranth pink#F19CBB95%61%73%338°75%78%35%95%Amaranth purple#AB274F67%15%31%342°63%41%77%67%
16Amaranth purple#AB274F67%15%31%342°63%41%77%67%Amaranth red#D3212D83%13%18%356°73%48%84%83%
17Amaranth red#D3212D83%13%18%356°73%48%84%83%Amazon#3B7A5723%48%34%147°35%36%52%48%
18Amazon#3B7A5723%48%34%147°35%36%52%48%Amber (SAE/ECE)#FF7E00100%49%0%30°100%50%100%100%
19Amber#FFBF00100%75%0%45°100%50%100%100%Android green#A4C63964%78%22%74°55%50%71%78%
20Amber (SAE/ECE)#FF7E00100%49%0%30°100%50%100%100%Antique brass#CD957580%58%46%22°47%63%43%80%
21Amethyst#9966CC60%40%80%270°50%60%50%80%Antique fuchsia#915C8357%36%51%316°22%46%37%57%
22Android green#A4C63964%78%22%74°55%50%71%78%Antique ruby#841B2D52%11%18%350°66%31%80%52%
23Antique brass#CD957580%58%46%22°47%63%43%80%Antique white#FAEBD798%92%84%34°78%91%14%98%
24Antique bronze#665D1E40%36%12%53°55%26%71%40%Ao (English)#0080000%50%0%120°100%25%100%50%
25Antique fuchsia#915C8357%36%51%316°22%46%37%57%Apple green#8DB60055%71%0%74°100%36%100%71%
26Antique ruby#841B2D52%11%18%350°66%31%80%52%Apricot#FBCEB198%81%69%24°90%84%29%98%
27Antique white#FAEBD798%92%84%34°78%91%14%98%Aqua#00FFFF0%100%100%180°100%50%100%100%
28Ao (English)#0080000%50%0%120°100%25%100%50%Aquamarine#7FFFD450%100%83%160°100%75%50%100%
29Apple green#8DB60055%71%0%74°100%36%100%71%Arctic lime#D0FF1482%100%8%72°100%54%92%100%
30Apricot#FBCEB198%81%69%24°90%84%29%98%Army green#4B532029%33%13%69°44%23%61%33%
31Aqua#00FFFF0%100%100%180°100%50%100%100%Artichoke#8F977956%59%47%76°13%53%20%59%
32Aquamarine#7FFFD450%100%83%160°100%75%50%100%Arylide yellow#E9D66B91%84%42%51°74%67%54%91%
33Arctic lime#D0FF1482%100%8%72°100%54%92%100%Ash gray#B2BEB570%75%71%135°8%72%6%75%
34Army green#4B532029%33%13%69°44%23%61%33%Asparagus#87A96B53%66%42%93°26%54%37%66%
35Artichoke#8F977956%59%47%76°13%53%20%59%Atomic tangerine#FF9966100%60%40%20°100%70%60%100%
36Arylide yellow#E9D66B91%84%42%51°74%67%54%91%Auburn#A52A2A65%16%16%59%41%75%65%
37Ash gray#B2BEB570%75%71%135°8%72%6%75%Aureolin#FDEE0099%93%0%56°100%50%100%99%
38Asparagus#87A96B53%66%42%93°26%54%37%66%Azure#007FFF0%50%100%210°100%50%100%100%
39Atomic tangerine#FF9966100%60%40%20°100%70%60%100%Azure (X11/web color)#F0FFFF94%100%100%180°100%97%6%100%
40Auburn#A52A2A65%16%16%59%41%75%65%
41Aureolin#FDEE0099%93%0%56°100%50%100%99%
42Avocado#56820334%51%1%81°95%26%98%51%
43Azure#007FFF0%50%100%210°100%50%100%100%
44Azure (X11/web color)#F0FFFF94%100%100%180°100%97%6%100%
Filter
Cell Formulas
RangeFormula
N3:W39N3=FILTER(A3:J44,ISNA(MATCH(A3:A44,L3:L7,0)),"")
Dynamic array formulas.
 
Upvote 0
Thanks,

Is there a way to include that formula in the following:

=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000)))
I have listed the data I want excluded in Teller sheet A4201:A4212
 
Upvote 0
Try

Excel Formula:
=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$5000,'Teller Statistics (Historic)'!A4201:A4212,0)))
 
Upvote 0
Try

Excel Formula:
=FILTER('Teller Statistics (Historic)'!B$1:M$5000,ISNUMBER(SEARCH(B4200,'Teller Statistics (Historic)'!A$1:A$5000))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$5000,'Teller Statistics (Historic)'!A4201:A4212,0)))

Unfortunately it did not work the data i want excluded is still in my filter results
 
Upvote 0
Unfortunately it did not work the data i want excluded is still in my filter results
I don't have your whole data, your exclusion list or the contents of cell B4200 or know just what you are trying to do. I may have put something in the wrong place or misinterpreted your formula intent. However, see if this smaller sample is on the right track and, if so, if you can adapt it.


Brentsa.xlsm
ABCD
1Absolute Zero#0048BA0%28%
2Acid green#B0BF1A69%75%
3Aero#7CB9E849%73%
4Aero blue#C0E8D575%91%
5African violet#B284BE70%52%
6Air superiority blue#72A0C145%63%
7Alabaster#EDEAE093%92%
8Alice blue#F0F8FF94%97%
9Alloy orange#C4621077%38%
10Almond#EFDECD94%87%
11
12
13
14
15Exclude
16Aero
17Alabaster
18Alice blue
19
20
Teller Statistics (Historic)


I want to filter rows 1:10 (columns B:D) for everything that contains an "e" in column A (that is, everything except Almond) but also exclude anything in A16:A20
This seems to me to do that.

Brentsa.xlsm
BCDE
1
2
3e#0048BA0%28%
4#B0BF1A69%75%
5#C0E8D575%91%
6#B284BE70%52%
7#72A0C145%63%
8#C4621077%38%
9
Filter
Cell Formulas
RangeFormula
C3:E8C3=FILTER('Teller Statistics (Historic)'!B$1:D$10,ISNUMBER(SEARCH(B3,'Teller Statistics (Historic)'!A$1:A$10))*ISNA(MATCH('Teller Statistics (Historic)'!A$1:A$10,'Teller Statistics (Historic)'!A16:A20,0)))
Dynamic array formulas.
 
Upvote 0
Solution
Thanks is seems as if when I tried to retype the formula I missed a bracket or two so that is why my formula was not working. it is working perfectly thanks for your time. it's much appreciated.
 
Upvote 0
You're welcome. Glad you got it working. :)

Not sure if you are aware, but instead of retyping formulas from the forum you can copy/paste using this icon at the top right of formulas or vba code.
1602625017147.png



.. or even all values & formulas from one of the XL2BB screen shots:
1602625094475.png
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top