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 wrote:

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" wrote:

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