Excel formula
 

I have a list of about 400 last names that I want to use to find in those
names in other lists I have by doing an advanced filter. Many of the names
are only a few letters and if I use them as is, as my criteria list, I get
those names plus any that start with those letters. I know I can format the
names so that it will only pick out the exact name on my list by typing
="="the name". I don't want to do that with all 400 names. How can I format
the first name on the list and copy or drag it down with each row changing to
the name on that row?
Example:
Adam format like this ="=adam"
Gil ="=Gil"
Wood

Excel formula
 

Select data range say A2:A1000 and from DataFilterAdvanced filterCriteria
range you can specify the 400 name range say D1:D400

If this post helps click Yes
---------------
Jacob Skaria


"sleepless" wrote:

I have a list of about 400 last names that I want to use to find in those
names in other lists I have by doing an advanced filter. Many of the names
are only a few letters and if I use them as is, as my criteria list, I get
those names plus any that start with those letters. I know I can format the
names so that it will only pick out the exact name on my list by typing
="="the name". I don't want to do that with all 400 names. How can I format
the first name on the list and copy or drag it down with each row changing to
the name on that row?
Example:
Adam format like this ="=adam"
Gil ="=Gil"
Wood

Excel formula
 

I'm not sure I'm expressing my problem correctly. I know how to selct the
data and criteria ranges but if I use the names in my list without formatting
them with the ="= " - I get names that start with the names in my list -
Adam gives me Adam and Adamson and Adams, etc. I guess my problem is more
how to format the 400 names in my list so I will get just those names.

"sleepless" wrote:

I have a list of about 400 last names that I want to use to find in those
names in other lists I have by doing an advanced filter. Many of the names
are only a few letters and if I use them as is, as my criteria list, I get
those names plus any that start with those letters. I know I can format the
names so that it will only pick out the exact name on my list by typing
="="the name". I don't want to do that with all 400 names. How can I format
the first name on the list and copy or drag it down with each row changing to
the name on that row?
Example:
Adam format like this ="=adam"
Gil ="=Gil"
Wood

Excel formula
 

I'd use another column.

With your list starting in x2 (headers in X1), then duplicate the headers in y1
and use a formula like:

="="&x2
and drag down.

Then use this helper column as the advanced filter criteria range.

sleepless wrote:

I have a list of about 400 last names that I want to use to find in those
names in other lists I have by doing an advanced filter. Many of the names
are only a few letters and if I use them as is, as my criteria list, I get
those names plus any that start with those letters. I know I can format the
names so that it will only pick out the exact name on my list by typing
="="the name". I don't want to do that with all 400 names. How can I format
the first name on the list and copy or drag it down with each row changing to
the name on that row?
Example:
Adam format like this ="=adam"
Gil ="=Gil"
Wood

--

Dave Peterson

Excel formula
 

Thanks, it worked

"Dave Peterson" wrote:

I'd use another column.

With your list starting in x2 (headers in X1), then duplicate the headers in y1
and use a formula like:

="="&x2
and drag down.

Then use this helper column as the advanced filter criteria range.

sleepless wrote:

I have a list of about 400 last names that I want to use to find in those
names in other lists I have by doing an advanced filter. Many of the names
are only a few letters and if I use them as is, as my criteria list, I get
those names plus any that start with those letters. I know I can format the
names so that it will only pick out the exact name on my list by typing
="="the name". I don't want to do that with all 400 names. How can I format
the first name on the list and copy or drag it down with each row changing to
the name on that row?
Example:
Adam format like this ="=adam"
Gil ="=Gil"
Wood

--

Dave Peterson
.