ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   advanced filter - can't match a long text cell (https://www.excelbanter.com/excel-discussion-misc-queries/25454-advanced-filter-cant-match-long-text-cell.html)

simpsons_rule

advanced filter - can't match a long text cell
 
Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..

Dave Peterson

Debra Dalgleish has a work around for autofilter at:
http://www.contextures.com/xlautofilter02.html#String

Maybe it'll give you an idea.

(I'm having trouble connecting to her site right now.)

simpsons_rule wrote:

Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..


--

Dave Peterson

Debra Dalgleish

Thanks for the alert -- it's back now.

Dave Peterson wrote:
Debra Dalgleish has a work around for autofilter at:
http://www.contextures.com/xlautofilter02.html#String

Maybe it'll give you an idea.

(I'm having trouble connecting to her site right now.)

simpsons_rule wrote:

Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


Dave Peterson

Oops. I was going to send an email, but got distracted by, well, something.



Debra Dalgleish wrote:

Thanks for the alert -- it's back now.

Dave Peterson wrote:
Debra Dalgleish has a work around for autofilter at:
http://www.contextures.com/xlautofilter02.html#String

Maybe it'll give you an idea.

(I'm having trouble connecting to her site right now.)

simpsons_rule wrote:

Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


--

Dave Peterson

simpsons_rule

Thanks for the info folks. But The strings I am matching on are less than 255
characters long (no more than about 30 characters). The only other thing I
can think of, maybe the advanced filter criteria can't handle some characters
(the strings I am trying to match on will have period chars, e.g
123.ABC.XYZ). Any ideas?

"simpsons_rule" wrote:

Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..


Debra Dalgleish

It should work with period characters. What text is in the criteria
string, and what unexpected results are being returned?

simpsons_rule wrote:
Thanks for the info folks. But The strings I am matching on are less than 255
characters long (no more than about 30 characters). The only other thing I
can think of, maybe the advanced filter criteria can't handle some characters
(the strings I am trying to match on will have period chars, e.g
123.ABC.XYZ). Any ideas?

"simpsons_rule" wrote:


Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


simpsons_rule

Thanks Debra,
I think I know what's happening now. I am getting
matches for all 4 columns correctly. But the advanced filter gives me the
results in alphabetical order. For example, if one of my criteria cells is
"WEEKLY.LOW.INTENSITY" and the next cell in the same column is
"WEEKLY.HIGH.INTENSITY", I get the correct rows OK, but in alphabetical
order. So in the filtered view, WEEKLY.HIGH.INTENSITY comes first. I was
hoping that the filtered view would maintain the same order as the criteria.
Is there any way to do this? I could work around it by sorting everything
aphabetically before doing my compare, but this really complicates things.


"Debra Dalgleish" wrote:

It should work with period characters. What text is in the criteria
string, and what unexpected results are being returned?

simpsons_rule wrote:
Thanks for the info folks. But The strings I am matching on are less than 255
characters long (no more than about 30 characters). The only other thing I
can think of, maybe the advanced filter criteria can't handle some characters
(the strings I am trying to match on will have period chars, e.g
123.ABC.XYZ). Any ideas?

"simpsons_rule" wrote:


Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



Debra Dalgleish

An advanced filter doesn't sort the results. Is this part of a macro? If
so, maybe the results are being sorted elsewhere in the code.

simpsons_rule wrote:
Thanks Debra,
I think I know what's happening now. I am getting
matches for all 4 columns correctly. But the advanced filter gives me the
results in alphabetical order. For example, if one of my criteria cells is
"WEEKLY.LOW.INTENSITY" and the next cell in the same column is
"WEEKLY.HIGH.INTENSITY", I get the correct rows OK, but in alphabetical
order. So in the filtered view, WEEKLY.HIGH.INTENSITY comes first. I was
hoping that the filtered view would maintain the same order as the criteria.
Is there any way to do this? I could work around it by sorting everything
aphabetically before doing my compare, but this really complicates things.


"Debra Dalgleish" wrote:


It should work with period characters. What text is in the criteria
string, and what unexpected results are being returned?

simpsons_rule wrote:

Thanks for the info folks. But The strings I am matching on are less than 255
characters long (no more than about 30 characters). The only other thing I
can think of, maybe the advanced filter criteria can't handle some characters
(the strings I am trying to match on will have period chars, e.g
123.ABC.XYZ). Any ideas?

"simpsons_rule" wrote:



Hi,
I have an advanced filter set up to match on the contents of 4 columns,
using the criteria =("=" & text) . It works fine, except on one column the
text is quite long, and the match only seems to work on part of the text. So
I get matches where the the first part of the text matches, but the end of
the text is different. How can I set up the criteria to get an exact match on
the text regardless of length? Thanks..
--
In the nation of the blind, the one eyed man is king..


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



All times are GMT +1. The time now is 05:42 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com