variant criteria name to filter

[Steve]

morning all.
I received a filter macro from here last October, 2008. Over the past 11
months or so, I've been modifying it as needed to meet various needs, and up
until now, all of my mod's have worked great.
I know I posted on this previously, but it was never answered due to my not
understanding what I was trying to explain.
The filter tool in excel looks for a specific statement based on the
criteria selected.
For my need, there are times when I want to look at a name that's LIKE the
criteria-- but not necessary the same, EXACTLY spelled name. Say, there are
names that are missing periods, for middle initials, or the date in one use
is configured mm/dd/yy, and another is mm/dd/yyyy, or Month-Day-Year, or
have an ampersand instead of and, etc.....
Presently, my filter ignores those-- as it should, I suppose-- and if it
can't find the exact match, there is no match.

My code is:

Sub FilterA()

Dim wks As Worksheet

mv = Range("f2").End(xlDown).value
' this sets the criteria for the ChgAppl#.

mv1 = Range("a2").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

For Each wks In ActiveWorkbook.Worksheets

If LCase(Left(wks.Name, 3)) Like "sum" Then

With wks
Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.


Sheets(wks.Name).Range("A8:F8").AutoFilter field:=3,
Criteria1:=mv1
'this takes in to account the owner name for a filter.
End With

End If

Next wks
End Sub

My goal would be to do something like--
if mv1 like field then
...........
end if

I've tried a few ideas I had, and none of them worked. And it's been several
weeks since I tried them, so I can't remember specifics at this point.

Thank you for your helps.
Best.

 

[Joel]

I went back to you r old posting to ss if there was a better discriptyion of
what you wanted. I found this posting

http://www.microsoft.com/office/com...7606&mid=3cd74e0f-09b9-4c3c-ba28-6ae2863b7606

the reason I went back was this line in your latest posting

If LCase(Left(wks.Name, 3)) Like "sum" Then

The "sum" didn't make sense. A like requires a wildcard "*" and the line
above doesn't have the "*". the "*" say any characters foloowing the "sum".
I think you need the following

If LCase(Left(wks.Name, 3)) Like "sum*" Then

Not sure if this will solve all you problems, but it is a start. this says
any string starting with "sum". Now if "sum" isn't at the beginning of the
string then you may need to use this

If LCase(Left(wks.Name, 3)) Like "*sum*" Then

If you sum sometimes have upper and lower case leeters (like previous
posting then this might work

If LCase(Left(wks.Name, 3)) Like "*[sS][uU][mM]*" Then

 

[Dave Peterson]

But you're only looking at the last (leftmost) 3 characters.

If LCase(Left(wks.Name, 3)) Like "sum" Then
would be more usually written as:
If LCase(Left(wks.Name, 3)) = "sum" Then

And using lcase() means that you don't have to worry about something like:
"*[sS][uU][mM]*"

I went back to you r old posting to ss if there was a better discriptyion of
what you wanted. I found this posting

http://www.microsoft.com/office/com...7606&mid=3cd74e0f-09b9-4c3c-ba28-6ae2863b7606

the reason I went back was this line in your latest posting

If LCase(Left(wks.Name, 3)) Like "sum" Then

The "sum" didn't make sense. A like requires a wildcard "*" and the line
above doesn't have the "*". the "*" say any characters foloowing the "sum".
I think you need the following

If LCase(Left(wks.Name, 3)) Like "sum*" Then

Not sure if this will solve all you problems, but it is a start. this says
any string starting with "sum". Now if "sum" isn't at the beginning of the
string then you may need to use this

If LCase(Left(wks.Name, 3)) Like "*sum*" Then

If you sum sometimes have upper and lower case leeters (like previous
posting then this might work

If LCase(Left(wks.Name, 3)) Like "*[sS][uU][mM]*" Then

morning all.
I received a filter macro from here last October, 2008. Over the past 11
months or so, I've been modifying it as needed to meet various needs, and up
until now, all of my mod's have worked great.
I know I posted on this previously, but it was never answered due to my not
understanding what I was trying to explain.
The filter tool in excel looks for a specific statement based on the
criteria selected.
For my need, there are times when I want to look at a name that's LIKE the
criteria-- but not necessary the same, EXACTLY spelled name. Say, there are
names that are missing periods, for middle initials, or the date in one use
is configured mm/dd/yy, and another is mm/dd/yyyy, or Month-Day-Year, or
have an ampersand instead of and, etc.....
Presently, my filter ignores those-- as it should, I suppose-- and if it
can't find the exact match, there is no match.

My code is:

Sub FilterA()

Dim wks As Worksheet

mv = Range("f2").End(xlDown).value
' this sets the criteria for the ChgAppl#.

mv1 = Range("a2").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

For Each wks In ActiveWorkbook.Worksheets

If LCase(Left(wks.Name, 3)) Like "sum" Then

With wks
Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.


Sheets(wks.Name).Range("A8:F8").AutoFilter field:=3,
Criteria1:=mv1
'this takes in to account the owner name for a filter.
End With

End If

Next wks
End Sub

My goal would be to do something like--
if mv1 like field then
...........
end if

I've tried a few ideas I had, and none of them worked. And it's been several
weeks since I tried them, so I can't remember specifics at this point.

Thank you for your helps.
Best.

 

[Dave Peterson]

I'm not sure what mv1 is, but you can use:

Criteria1:="*" & mv1 & "*"
for contains.

Criteria1:=mv1 & "*"
for starts with.

Criteria1:="*" & mv1
for ends with.

After that, I think you'll have to use pick apart that name (mid's, left's,
right's, instr's may help).

 

[Steve]

Part of the issue that I'm dealing with isn't the worksheet name.
It's the field's value in the autofilter.
I.e., I need to find something to look at in the field, based on my criteria.
Thus, the Like statement.
The autofilter is a really great tool, and normally it works fine, fantastic
actually.
The issue is that there are times when the names on one worksheet do not
match the name on the other. As stated in the post from yesterday- there are
times when a period is missing in the middle initial of a name on the source
sheet, and it exists on the destination sheet. When I go to activate the
autofilter, it can't find an EXACT match, so it ignores the one that's like
it.

Thus, my question then becomes--
1- can the 'field' component of autofilter be set to recognize a value
that's LIKE the source? E.g.,

Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv

In this line above, 'field' is set to look at the first column of the
destination sheet.
The criteria is set to look at a predetermined variable, from the source
column- F - on my source sheet, established in my macro.
e.g., mv = Range("f2").End(xlDown).value

Can this be done for the 'Field'?
My attempts to date have failed.

I went back to you r old posting to ss if there was a better discriptyion of
what you wanted. I found this posting

http://www.microsoft.com/office/com...7606&mid=3cd74e0f-09b9-4c3c-ba28-6ae2863b7606

the reason I went back was this line in your latest posting

If LCase(Left(wks.Name, 3)) Like "sum" Then

The "sum" didn't make sense. A like requires a wildcard "*" and the line
above doesn't have the "*". the "*" say any characters foloowing the "sum".
I think you need the following

If LCase(Left(wks.Name, 3)) Like "sum*" Then

Not sure if this will solve all you problems, but it is a start. this says
any string starting with "sum". Now if "sum" isn't at the beginning of the
string then you may need to use this

If LCase(Left(wks.Name, 3)) Like "*sum*" Then

If you sum sometimes have upper and lower case leeters (like previous
posting then this might work

If LCase(Left(wks.Name, 3)) Like "*[sS][uU][mM]*" Then

morning all.
I received a filter macro from here last October, 2008. Over the past 11
months or so, I've been modifying it as needed to meet various needs, and up
until now, all of my mod's have worked great.
I know I posted on this previously, but it was never answered due to my not
understanding what I was trying to explain.
The filter tool in excel looks for a specific statement based on the
criteria selected.
For my need, there are times when I want to look at a name that's LIKE the
criteria-- but not necessary the same, EXACTLY spelled name. Say, there are
names that are missing periods, for middle initials, or the date in one use
is configured mm/dd/yy, and another is mm/dd/yyyy, or Month-Day-Year, or
have an ampersand instead of and, etc.....
Presently, my filter ignores those-- as it should, I suppose-- and if it
can't find the exact match, there is no match.

My code is:

Sub FilterA()

Dim wks As Worksheet

mv = Range("f2").End(xlDown).value
' this sets the criteria for the ChgAppl#.

mv1 = Range("a2").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

For Each wks In ActiveWorkbook.Worksheets

If LCase(Left(wks.Name, 3)) Like "sum" Then

With wks
Sheets(wks.Name).Range("A8:F8").AutoFilter field:=1, Criteria1:=mv
'this takes in to acct the chg appl# for a filter.


Sheets(wks.Name).Range("A8:F8").AutoFilter field:=3,
Criteria1:=mv1
'this takes in to account the owner name for a filter.
End With

End If

Next wks
End Sub

My goal would be to do something like--
if mv1 like field then
...........
end if

I've tried a few ideas I had, and none of them worked. And it's been several
weeks since I tried them, so I can't remember specifics at this point.

Thank you for your helps.
Best.

 

[Steve]

So, let me see if I'm grasping your point..... the field element would be
constant-- period-- always the numeric value of the column (field that I want
to be viewed for my criteria)?
And it's my criteria that'd be the variant? Ok, that's making sense to
me.....
To answer your question-- mv, and mv1 are the values associated with the
column-- hence the range element defined near the top of the macro. I.e.,

mv = Range("f2").End(xlDown).value
' this sets the criteria for the ChgAppl#.

mv1 = Range("a2").End(xlDown).value
'this is my add-on to set a second criteria filter- Name of owner.

An explanation of the purpose for all this--

In my filter, I'm filtering two columns' values. I then activate the macro,
and it then filters a second, or destination worksheet for me. I have two
subtotal worksheet functions that then tell me the values of the filtered
elements. I then have an if worksheet function to tell me if those subtotal
functions match or not. It is indeed a lengthy routine, but it works.
The reason that I've done it this way is that my source page is a detailed
breakdown of all the elements, and the destination page is a summary of those
elements. Once I verify that both match, I then take the detailed page, and
copy/paste it into our sql DB for public access.

You should've seen the old way..... inaccurate, and extremely tedious.

 

[Dave Peterson]

If you're always applying the filter arrows to A:F (6 columns) and you're always
looking in the first column of that range (column A in this situation), then the
field would be 1.

But if your data had names in column A, cities in column B, states in column C,
zips in column D and you wanted to filter columns A:F by Date (column D), then
the field would be 4.

Your code could determine what that field number is -- if you know the rules to
apply.

On the other hand, if your names were sometimes in column A, sometimes in column
D and sometimes in column F, then you're going to have trouble--I would think
that you'd want to filter each of those fields and extra whatever info you
could.

(Actually, I'd do my best to make the data nice. Clean it up before your macro
even sees it. Yep, this is a miserable task.)

=======
It sounds like you may be filtering by all the unique values in a field (or
multiple fields).

Instead of looping through those unique values, you may want to look at
pivottables. You'll be able to get subtotals for each unique value pretty
quickly.

 

[Dave Peterson]

I'm not sure how Date go in there!

But if your data had names in column A, cities in column B, states in column C,
zips in column D and you wanted to filter columns A:F by *ZIP* (column D), then
the field would be 4.

 

[Steve]

hi again Dave,
Ok, let's see....
1- my data, for this macro, will always be a name, and a 5 digit number. The
number is a non-issue here, and I seldom, if ever have any trouble with it.
And the times I did have troubles, I was able to resolve them rather quickly.

2- Thus, we're to the name.
The name is the variant, and sometimes has had the punctuation removed from
it because of how our sql db is configured. I recently was able to get that
fixed, and now only remove certain punctuation-- far more limited a cleaning
than before. Unfortunately, our DB Mgr really wants all punctuation, of all
forms removed. I finally stopped haggling with them over it, and just let it
go.

So, for example. there are times, from older datasets, we'll have
John C. Smith
Or
John C Smith

While seemingly innocuous, the period has been removed, and an extra space
is now between the C, and Smith.

The data on my source page- I'll call Pg5 for simplicity- shows John C Smith.
On my destination sheet- I'll call Sum-- the name is John C. Smith.

The filter will look for John C Smith, and completely miss John C. Smith.
It's my desire-- until I can get all of the data cleaned up, as you've
encouraged, to have the source data filter recognize the destination data.
Based on what I'm understanding of your post, it sounds like I'm asking for
something that's not possible, or if it is, it'd be a really ugly workaround.

I will look in to the pivot table. I've tinkered with it only once, and
never went back. Mostly because I didn't understand what I was doing, and
wasn't all that motivated to work through it at the time.

 

[Dave Peterson]

The real problem I've had when cleaning data like this:
John C. Smith
is that sometimes, it's entered like:
John C. Smith
Smith, John C.
Smith John C
And lots of variations with Mr., Mr, Dr, Dr., Esquire, ...

You could remove all the extra spaces and periods with a formula:

=trim(substitute(a1,"."," "))

But I've never been able to create a formula that would fix every variation.
I'll use lots of manual tools (edit|replaces and formulas with copy|paste
special|values). And then I'll try to look for all the stuff I didn't consider.

You may want to consider using multiple columns in excel (first, last, middle,
title, ...) and then use a formula to create your key.

 

[Steve]

Good morning Dave....
hmmm... this sounds suspiciously familiar. Oh, that's right, I'm not Hal.
Thank God.......
lol....
Ok,
Yea, it's looking like I'll just need to deal with my issues then.
For a long time, I'd had a clean data set. Then the boss said he wanted us
to do our own database entries. At first it was easy to ignore, then it
became a little more involved.
I was eventually able to create my present configuration, and with the help
of some of you here in the newsgroups, came up with a sweet filter macro late
last year.
Then we began having issues with the database-- data limitations,
punctuation limits, etc.... While I've been able to get our DB programmer to
remove the limiting factors, some datasets from the previous iteration still
remain, and I'd hoped I could make a workaround so that they'd not be so
painful.
What you describe below is what I'd been doing-- aside from the
trim(substitute ()) equation/function.
Pulling the datasets even further apart as you mention is not an option--
only because our sqlDB isn't configured that way.

While we are in the middle of a DB update, and modification, it hasn't come
far enough to make this simpler yet..... yet being the key word.

Well, if you're having the same data cleaning issues that I am, then I'm in
good company, and am willing to say ok, and live with what I've got for now,
and continue using my manual tools.
Thank you very much for working through this with me, I really............
really.......... really......... appreciate it.
Have a great day.
chat with you on the next one......
Best,
SteveB.

 

[Dave Peterson]

It's a miserable job to clean up this kind of stuff. And it's really easy to
make a catastrophic mistake.

Make sure you save often--and I'd recommend that you save as a new name
(myList_yyyymmdd-hhmmss.xls) every so often.

Then when you screw up and "fix" things the wrong way, you can go back to one of
those version. You may not lose all your work.

 

[Steve]

Agreed.... and indeed done already.