Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching 2 lists
On May 18, 10:27*am, wrote:
Hi All, I was wondering if I can get some help with a probelm I've encountered. I have 2 lists - one list has a bunch of cities listed in it, separated by space, and the 2nd list has only one city listed there. I want to to create a column next to the first list and put in a formula stating that one of the cities listed in the 1st list is actually on the 2nd list. An example would be better way to describe this: List1 Column A, Column B Middletown Athens Atlanta LA NY Chicago, "Yes" List2 ColumnA Madison Lexington Savannah Danbury Providence Atlanta ... So, from above, in column B of list one, my formula would look at the cities in Column A and check against List2 column A and say "Yes" if any one of the Cities in List1 Column A appear in List2 Column A. The reason I have a "Yes" in Column B of list 2 is because Atlanta appears in List2. Otherwise, it would be a "No"... Thanks in advance. Haas, Assuming that the text for List 1 is in A1 and the text for List 2 starts in A3, you could use the formula listed below, which I placed in B3. The FIND formula returns whether the desired string can be found, returning the starting character position of the matching text. If no text is found, the FIND formula returns an error. Hence, if an error is returned, then the List 2 city is not found in List 1, but if an error is not returned, then the List 2 city is found in List 1. =IF(ISERROR(FIND(A3,$A$1,1)),"No","Yes") Best, Matthew Herbert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Matching 2 lists
On May 18, 11:37*am, wrote:
On May 18, 10:27*am, wrote: Hi All, I was wondering if I can get some help with a probelm I've encountered. I have 2 lists - one list has a bunch of cities listed in it, separated by space, and the 2nd list has only one city listed there. I want to to create a column next to the first list and put in a formula stating that one of the cities listed in the 1st list is actually on the 2nd list. An example would be better way to describe this: List1 Column A, Column B Middletown Athens Atlanta LA NY Chicago, "Yes" List2 ColumnA Madison Lexington Savannah Danbury Providence Atlanta ... So, from above, in column B of list one, my formula would look at the cities in Column A and check against List2 column A and say "Yes" if any one of the Cities in List1 Column A appear in List2 Column A. The reason I have a "Yes" in Column B of list 2 is because Atlanta appears in List2. Otherwise, it would be a "No"... Thanks in advance. Haas, Assuming that the text for List 1 is in A1 and the text for List 2 starts in A3, you could use the formula listed below, which I placed in B3. *The FIND formula returns whether the desired string can be found, returning the starting character position of the matching text. *If no text is found, the FIND formula returns an error. *Hence, if an error is returned, then the List 2 city is not found in List 1, but if an error is not returned, then the List 2 city is found in List 1. =IF(ISERROR(FIND(A3,$A$1,1)),"No","Yes") Best, Matthew Herbert- Hide quoted text - - Show quoted text - Thanks for the above but that doesn't seem to work. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
matching/comparing lists | Excel Worksheet Functions | |||
Matching lists | Excel Programming | |||
Matching two lists | Excel Worksheet Functions | |||
Matching 2 lists | Excel Worksheet Functions | |||
Matching two lists. | Excel Discussion (Misc queries) |