Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |