Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case sensitive filter
I was wonder how to go about the following:
I have a list of characters, and I want to be able to have a list of all of the unique characters. The built in advanced filter function is not case sensitive. Is there anyway to make it case sensitive, or is there another way to do this? Any help is much appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case sensitive filter
Let's say we want to filter column A by any criteria AND only upper case. In
the second row of a helper column enter: =EXACT(A2,UPPER(A2)) and copy down. Of course, you should put a label in the first cell of the helper column. This formula returns TRUE only if A1 is upper case. Switch on autofilter and, using the helper column, you can filter to show either the uppers or the lowers. -- Gary's Student gsnu200708 "Shudder777" wrote: I was wonder how to go about the following: I have a list of characters, and I want to be able to have a list of all of the unique characters. The built in advanced filter function is not case sensitive. Is there anyway to make it case sensitive, or is there another way to do this? Any help is much appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case sensitive filter
Try this as a start. you need to use a couple of helper columns. Try putting
=CODE(a2) in b2 if your list is in collumn a. When you copy it down it will put the ACSII code in column b. I was not able to auto sort on this column directly but if you copy and paste values using paste special in column c you can auto sort unique values on that new collumn. Column A should show unique values taking into account upper and lower case letters. I think there might be a better way but can not come up with it at the moment -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Shudder777" wrote: I was wonder how to go about the following: I have a list of characters, and I want to be able to have a list of all of the unique characters. The built in advanced filter function is not case sensitive. Is there anyway to make it case sensitive, or is there another way to do this? Any help is much appreciated. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case sensitive filter
Try this:
=IF(ISERR(SMALL(IF(FREQUENCY(CODE($A$2:$A$10),CODE ($A$2:$A$10)),ROW(INDIRECT("1:"&ROWS($A$2:$A$10))) ),ROWS($1:1))),"",INDEX($A$2:$A$10,SMALL(IF(FREQUE NCY(CODE($A$2:$A$10),CODE($A$2:$A$10)),ROW(INDIREC T("1:"&ROWS($A$2:$A$10)))),ROWS($1:1)))) ctrl+shift+enter, not just enter Adjust your range to suit "Shudder777" wrote: I was wonder how to go about the following: I have a list of characters, and I want to be able to have a list of all of the unique characters. The built in advanced filter function is not case sensitive. Is there anyway to make it case sensitive, or is there another way to do this? Any help is much appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation ... Case Sensitive? | Excel Discussion (Misc queries) | |||
Case sensitive filtering | Excel Discussion (Misc queries) | |||
Getting a case sensitive match? | Excel Worksheet Functions | |||
Case Sensitive w/ IF | Excel Worksheet Functions | |||
Case sensitive vlookup | Excel Discussion (Misc queries) |