Advanced Filter Criteria ?ion

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Hi All,

I am working with Excel 2013 & I want to 'Advance Filter' with more than one Criteria in a Cell w/o 'Advanced Filtering' for a 2nd time based on another criteria in further reducing the produced list.

ie.

Criteria:

NameNumber
<>Charles

<tbody>
</tbody>

Now, if I want to also omit another name w/o Advance Filtering for a 2nd time, for example, to further omit <>Michael; How can I do this in its original cell?


Is there a way to join in that original criteria cell, <>Charles & <> Michael?

Many Thanks,

-Pin
:confused:
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Here is how I structured it.Raw data in A1 to B12 criteria in D1 to E2 and the output in D7

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">age</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">james</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style=";"><>charles</td><td style=";"><>Michael</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">jane</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">james</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">kane</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">charles</td><td style="text-align: right;;">27</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">charles</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">age</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">jane</td><td style="text-align: right;;">21</td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">16</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">charles</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style=";">jane</td><td style="text-align: right;;">29</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Michael</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">22</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">james</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style=";">kane</td><td style="text-align: right;;">21</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">kane</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style=";">jane</td><td style="text-align: right;;">21</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">kane</td><td style="text-align: right;;">13</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet6</p><br /><br />
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Hi Momentman,

I didn't realize you could duplicate headers and omit others in producing a result.

I'll have to try it out.

Would this method work for a 3rd for example?

Many thanks,

-Pin
 
Last edited:

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Yes it would, see the below

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Name</td><td style=";">age</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">Name</td><td style=";">Name</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">james</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style=";"><>charles</td><td style=";"><>Michael</td><td style=";"><>kane</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">jane</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">james</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">kane</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">charles</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">charles</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style=";">Name</td><td style=";">age</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">jane</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">charles</td><td style="text-align: right;;">26</td><td style="text-align: right;;"></td><td style=";">jane</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Michael</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">22</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">james</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style=";">jane</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">kane</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style=";">james</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet6</p><br /><br />
 

Pinaceous

Well-known Member
Joined
Jun 11, 2014
Messages
657
Wow Momentman! Thank you. Theoretically, then you can have numerous title heading duplicates for your criteria in producing your desired output and you do not have to represent all of the column headings as part of the criteria.

You have taught me a very valuable lesson. Much appreciated! Many thanks and G-Bless.

-Pin
 

Watch MrExcel Video

Forum statistics

Threads
1,123,535
Messages
5,602,217
Members
414,513
Latest member
junbuggle

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
Top