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.

 

[sanrv1f]

Hi Aladin

I got the desired result using your suggestion, could you explain, how it is working?

 

[ajm]

guys, could there be something else affecting my results? i am using 2007: would that cause any issues?

if i filter in place, i get one entry each for the four names in the list. so, my issue must be with the way i am trying to get the Y into the mix.

 

[Aladin Akyurek]

guys, could there be something else affecting my results? i am using 2007: would that cause any issues?

I did it on 2010. I'm not aware of any Advanced Filter bug 2007 might have.

 

[PATSYS]

I have 2007 and getting the same result as the TS using Aladin's solution.

 

[Aladin Akyurek]

Hi Aladin

I got the desired result using your suggestion, could you explain, how it is working?

Advanced Filter runs

=A2="Y"

together with (AND'ing with)

=COUNTIF($D$2:D2,D2)=1

for each record.

The latter lets Advanced Filter pick out a D item when its frequency is just 1 and skips other instances of that item (frequency is going up: 2, 3, etc.).

Does this clarify?

 

[Aladin Akyurek]

guys, could there be something else affecting my results? i am using 2007: would that cause any issues?

See the replies already given..

if i filter in place, i get one entry each for the four names in the list. so, my issue must be with the way i am trying to get the Y into the mix.

No, unless you are not following the recipe I outlined earlier for 'Copy to another location.'

 

[sanrv1f]

Advanced Filter runs

=A2="Y"

together with (AND'ing with)

=COUNTIF($D$2:D2,D2)=1

for each record.

The latter lets Advanced Filter pick out a D item when its frequency is just 1 and skips other instances of that item (frequency is going up: 2, 3, etc.).

Does this clarify?

Great, learning for the week!

Aladin, thanks much

One doubt, without any header, how does excel decides the columns to apply the filter, simply based on the formula?

 

[Aladin Akyurek]

Great, learning for the week!

Aladin, thanks much

One doubt, without any header, how does excel decides the columns to apply the filter, simply based on the formula?

The references the formula addresses suffice. Like in databases, such are construed as calculated fields. Instead of leaving the header field(s) blank/empty, we can put there header(s) other than the data area already have.

 

[ajm]

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.

 

[mikerickson]

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.