Filter function that must exclude specific data

Brentsa

New Member
Joined
Oct 3, 2013
Messages
39
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

Brentsa

New Member
Joined
Oct 3, 2013
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Brentsa

New Member
Joined
Oct 3, 2013
Messages
39
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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.
 

Brentsa

New Member
Joined
Oct 3, 2013
Messages
39
Office Version
  1. 365
Platform
  1. Windows
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,915
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,635
Members
410,861
Latest member
Victor96
Top