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?

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?

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?

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?

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?

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?

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?

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?

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?