Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a word(s) within a list
I have a list of entries and some have US city names and/or states. I want to
be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a word(s) within a list
This may not be 100% successful....
Assume you have a list of states/cities in the range F2:F7. The strings to check are in the range A2:An. Enter this formula in B2 and copy down as needed: (all on one line) =IF(COUNT(LOOKUP(2,1/SEARCH (" "&F$2:F$7&" "," "&A2&" "))),"Y","") -- Biff Microsoft Excel MVP "Philippa" wrote in message ... I have a list of entries and some have US city names and/or states. I want to be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a word(s) within a list
What happens if the data is George Washington. Is Washington the state or
the last name? "T. Valko" wrote: This may not be 100% successful.... Assume you have a list of states/cities in the range F2:F7. The strings to check are in the range A2:An. Enter this formula in B2 and copy down as needed: (all on one line) =IF(COUNT(LOOKUP(2,1/SEARCH (" "&F$2:F$7&" "," "&A2&" "))),"Y","") -- Biff Microsoft Excel MVP "Philippa" wrote in message ... I have a list of entries and some have US city names and/or states. I want to be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a word(s) within a list
Hi Philippa,
As your sample shows, you are interested in locating all entries whose last three characters are a space followed by a 2 letter state abbreviation. If you data is entered in that format then suppose the data is in column A and you list all of the state abbrreviations in the range F1:F50, then in a blank column enter the following array formula: =IF(OR(RIGHT(A1,3)=" "&$F$1:$F$50),"Yes","No") And copy it down as far as necessary. To enter it as an array press Shift Ctrl Enter instead of Enter. -- Cheers, Shane Devenshire Join http://setiathome.berkeley.edu/ and download a free screensaver to help search for extra terrestrial life. "Philippa" wrote: I have a list of entries and some have US city names and/or states. I want to be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
searching for a word(s) within a list
That's one of the reasons I said:
This may not be 100% successful.... -- Biff Microsoft Excel MVP "Joel" wrote in message ... What happens if the data is George Washington. Is Washington the state or the last name? "T. Valko" wrote: This may not be 100% successful.... Assume you have a list of states/cities in the range F2:F7. The strings to check are in the range A2:An. Enter this formula in B2 and copy down as needed: (all on one line) =IF(COUNT(LOOKUP(2,1/SEARCH (" "&F$2:F$7&" "," "&A2&" "))),"Y","") -- Biff Microsoft Excel MVP "Philippa" wrote in message ... I have a list of entries and some have US city names and/or states. I want to be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling State data from another sheet in workbook
This is an excellent formula you provided below, in which I've been using.
What I can't figure out is how to write the formula when pulling data from another sheet? Example, I'm currently collecting contact info from States which consist of primary and alternate POC's. One State may have 7 POC's and another 10. I received them as: Column A, Column B, Column C AL Primary Jonn Doe PA Primary Jonn Doe PA Alternate Jonn Doe I then consolidate all data and paste the info into sheet 1. For sheet 2, I simply have it setup in column B all 54 States and Territories abbr and in column C is where I would like to create the formula. On sheet 2, if column B1 is AL, then the formula in C1 will look up applicable info in sheet 1 and indicate the word "received", if info is in range. I'm not concern with the number of times AL is listed on sheet 1, just want the info to be recognized in sheet 2 as received. Please provide a formula for me if there is one. Thanks. "T. Valko" wrote: This may not be 100% successful.... Assume you have a list of states/cities in the range F2:F7. The strings to check are in the range A2:An. Enter this formula in B2 and copy down as needed: (all on one line) =IF(COUNT(LOOKUP(2,1/SEARCH (" "&F$2:F$7&" "," "&A2&" "))),"Y","") -- Biff Microsoft Excel MVP "Philippa" wrote in message ... I have a list of entries and some have US city names and/or states. I want to be able to identify those cells that have a match for a US city or state in the data. For example see the data below: Jackie Lexington AL James Jones Australia Floyd NE Jamie Salter If I use the list above and match that against a us city/st list I want only the 1st and 3rd entries returned as these are the only 3 that match the criteria and woud find a match in the US city State list. doe this make sense? Is this clear? Let me know. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
highlight the word when excel finds text you are searching for | Excel Worksheet Functions | |||
searching within list | Excel Discussion (Misc queries) | |||
Is Excel or Word better for holding and searching data | Excel Discussion (Misc queries) | |||
searching a cell for a contained text word | Charts and Charting in Excel |