Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Hello,
I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Hi Marylin
I cannot reproduce your problem. If I type Contains, then the number, it picks up the values from both single entry cells, and those with 2 numbers separated by an Alt+Enter to force the carriage return within the cell. -- Regards Roger Govier "Marilyn" wrote in message ... Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Thanks for the Reply Roger..... I created a new test file and was able to
recreate the issue. what format are you using. It seems to me that the cells with double entries are formatted as text and the one with one entry are formatted as numbers. I have tried to play around with different formats but I have not had much success. "Roger Govier" wrote: Hi Marylin I cannot reproduce your problem. If I type Contains, then the number, it picks up the values from both single entry cells, and those with 2 numbers separated by an Alt+Enter to force the carriage return within the cell. -- Regards Roger Govier "Marilyn" wrote in message ... Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Why dont you split that to two columns and then filter. To separate lines to
different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Thanks Jacob, but unfortunately the data needs to remain in one column.
"Jacob Skaria" wrote: Why dont you split that to two columns and then filter. To separate lines to different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Marilyn. Filter works on entire cell contents and not items within a cell.
Refer the below link http://contextures.com/xlautofilter02.html#Limits If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks Jacob, but unfortunately the data needs to remain in one column. "Jacob Skaria" wrote: Why dont you split that to two columns and then filter. To separate lines to different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Thanks again Jacob, but the contents of the article does not seem to apply.
Keep in mind that when I use the Contains criteria it does pick up the item within the cell since those cells have 2 sets of numbers separated by a carriage return. Thanks again. "Jacob Skaria" wrote: Marilyn. Filter works on entire cell contents and not items within a cell. Refer the below link http://contextures.com/xlautofilter02.html#Limits If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks Jacob, but unfortunately the data needs to remain in one column. "Jacob Skaria" wrote: Why dont you split that to two columns and then filter. To separate lines to different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Marilyn
Oops I missed the actual point. OK. I have tried this now.. It is nothing to do with the number of lines...'Contains' really means the value you are looking for is within the cell and 'equal' is the cell value itself. With a single entry 3.3 in a cell formatted as text and i typed a space after "3.3 ". It display this single number cell along with the 2 lined entry having 3.3.. OR Try with multiple conditions 'equal to' 3.3 OR 'contains' 3.3 which will list both rows... If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks again Jacob, but the contents of the article does not seem to apply. Keep in mind that when I use the Contains criteria it does pick up the item within the cell since those cells have 2 sets of numbers separated by a carriage return. Thanks again. "Jacob Skaria" wrote: Marilyn. Filter works on entire cell contents and not items within a cell. Refer the below link http://contextures.com/xlautofilter02.html#Limits If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks Jacob, but unfortunately the data needs to remain in one column. "Jacob Skaria" wrote: Why dont you split that to two columns and then filter. To separate lines to different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2003 Filtering Mistery
Jacob....I have found my answer and it indeed had to do with the formatting
of the column. Here's what had to be done. selected all the numbers in the column Choose the Text to Columns in the Data menu Selected Fixed Width On the 3 step of the Wizard selected Text as the format and clicked Finish. Now the Contains criteria picks up the cells with 2 lines and one line. Thanks for your help. "Jacob Skaria" wrote: Marilyn Oops I missed the actual point. OK. I have tried this now.. It is nothing to do with the number of lines...'Contains' really means the value you are looking for is within the cell and 'equal' is the cell value itself. With a single entry 3.3 in a cell formatted as text and i typed a space after "3.3 ". It display this single number cell along with the 2 lined entry having 3.3.. OR Try with multiple conditions 'equal to' 3.3 OR 'contains' 3.3 which will list both rows... If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks again Jacob, but the contents of the article does not seem to apply. Keep in mind that when I use the Contains criteria it does pick up the item within the cell since those cells have 2 sets of numbers separated by a carriage return. Thanks again. "Jacob Skaria" wrote: Marilyn. Filter works on entire cell contents and not items within a cell. Refer the below link http://contextures.com/xlautofilter02.html#Limits If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Thanks Jacob, but unfortunately the data needs to remain in one column. "Jacob Skaria" wrote: Why dont you split that to two columns and then filter. To separate lines to different columns select data from menu DataText to ColumnsDelimited by other Ctrl+j and Finish. If this post helps click Yes --------------- Jacob Skaria "Marilyn" wrote: Hello, I have an excel table which I often filter. The column that I filter by has numbers and some of the numbers have a carriage which means some cells have two lines instead of one and some cells just have one line. When I perform a Custom filter I choose Custom from the drop down list and select Contains and type desired number. The problem is that the Contains criteria only picks up the cells with 2 lines and omits the cells with 1 line that matches the filter. If I choose Equals then the Equals criteria only picks up the cells with 1 line and omits the cells with 2 lines. Example: The numbers with the quotes share one cell. "6.2 6.7" "3.4 5.3" 5.2 "1.1 3.3" 3.4 any ideas what can be done in this case? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help in filtering in excel 2003 | Excel Discussion (Misc queries) | |||
Help in Filtering in excel 2003 | Charts and Charting in Excel | |||
Excel 2003 Filtering | Excel Discussion (Misc queries) | |||
ADVANCED FILTERING IN EXCEL 2003 | Excel Discussion (Misc queries) | |||
Mistery digits on excel | Excel Discussion (Misc queries) |