advanced filter - can't match a long text cell

[Guest]

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..

 

[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.)

 

[Debra Dalgleish]

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

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.)

 

[Dave Peterson]

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

 

[Guest]

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?

 

[Debra Dalgleish]

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

 

[Guest]

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]

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.

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.


: