advanced filter, criteria range, hmm

[ajm]

folks, i am trying to create a unique list of names from a table containing our salesmen and their active accounts. In column A, I have marked all current salesmen by "Y". Their names appear in Column D. So, I want a unique list of the salesmen marked by a Y.

<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com)  -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2" align="left">
 <tr><th bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Active</font></th><th bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></th><th bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Slsm Nbr</font></th><th bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Salesman Name</font></th></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(315492)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">204</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Good Bloke</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(134166)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">231</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Good Bloke</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(433336)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">217</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Daised Duck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(235315)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">217</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Daised Duck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(456884)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">241</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Daised Duck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(170051)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">211</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">L Skywalker</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(476690)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">215</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">L Skywalker</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(166824)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">211</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">L Skywalker</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(102136)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">213</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">L Skywalker</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(431130)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">218</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Mikey Jackson</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(129859)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">213</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Mikey Jackson</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(430000)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">218</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Mikey Jackson</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="center" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Y</font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(456998)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">218</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Mikey Jackson</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(449313)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">206</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Rubber Neck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(528721)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">206</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Rubber Neck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(10512)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">206</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Rubber Neck</font></td></tr>
 <tr><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"> </font></td><td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">(451938)</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">206</font></td><td bgcolor="#FFFFFF" align="left" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Rubber Neck</font></td></tr>
</table>

using advanced filter, I select: copy to another location, i select the list range (columns A - D), enter the criteria range (Y)** , copy to location ("Sheet2"), and select unique records only. **I have set up a single cell named range with Y to be the criteria range.


I really only want the salesmens names in a unique list as the result but can't figure out how to get this list. I have used advanced filter before but not when the criteria to select from the list and the list are in different columns. can anyone correct me or perhaps offer a better way of achieving my unique list? the aim ios to then create a named range from the unique list to use in data validation selection box.

 

[Aladin Akyurek]

The data for an Advanced Filter list range is supposed to have a blank column on each side of the data and a unique header for every column.

Column B doesn't meet those criteria, putting a header in B1 would fix it.

Perhaps this is the issue.

I did asked to do that. Post #3 from ajm seems to imply that the suggestion has been followed...

 

[Aladin Akyurek]

I hate to be a downer guys, but i still can't get it to work. I know it should work and I can easily understand Aladin's explanation, but something is preventing me from getting the desired result. there must be something that is set differently or is formatted differently in my set up if you guys get it and i don't.

Would you try the following:

=TRIM(A2)="Y"

instead of:

=A2="Y"

 

[ajm]

this is weird. i am posting from my wife's pc at home. the advanced filter works the first time with Trim. its excel 2010. i will try trim when i get to work in a half hour or so. however, if i delete the destination range, add a new Y to Daised Duck, and run it again, it only gives me the initial three people - Bloke, Skywalker, and Jackson.

 

[ajm]

still no go on my work pc. i might pursue a different means to get my list: maybe a little macro to get the whole list, remove dupes, and delete the rows not starting with Y. Thanks aladin, mike, sanrv1f and patsys for you time and effort. i know how frustrating it can be to have something work on your own machine but not have it work on someone else's and then trying to talk them through it all. once again, thanks to all for the attempts to help.

 

[Aladin Akyurek]

this is weird. i am posting from my wife's pc at home. the advanced filter works the first time with Trim. its excel 2010. i will try trim when i get to work in a half hour or so. however, if i delete the destination range, add a new Y to Daised Duck, and run it again, it only gives me the initial three people - Bloke, Skywalker, and Jackson.

As you probably know, TRIM removes leading, trailing, and excessive spaces from an entry. Since adding TRIM appears to work at home, we must conclude that a Y in some cases is not a single char Y. When you re-run Advanced Filter, you must not let it use the choices of the previous run: Let it use modified List range...

still no go on my work pc. i might pursue a different means to get my list: maybe a little macro to get the whole list, remove dupes, and delete the rows not starting with Y.

Advanced Filter is a great tool. Don't give up.

Thanks aladin, mike, sanrv1f and patsys for you time and effort. i know how frustrating it can be to have something work on your own machine but not have it work on someone else's and then trying to talk them through it all. once again, thanks to all for the attempts to help.

You are welcome. Thanks for providing feedback.

 

[ajm]

When you re-run Advanced Filter, you must not let it use the choices of the previous run: Let it use modified List range...

when re-running the advanced filter, i would delete the columns from $I$1 across ( as i had nothing else on this tab). what do you mena by "modified list range"?

 

[Aladin Akyurek]

when re-running the advanced filter, i would delete the columns from $I$1 across ( as i had nothing else on this tab). what do you mena by "modified list range"?

Advanced Filter tends to keep/recall the specifications of the first run we enter for List range, Criteria range, etc.

When you change the data area and you run anew Advanced Filter on that data area, enter the specifications anew and don't let it use the specifications of the previous run.