|
Wildcard lookup
I'm not sure if this is possible with excel functions (and I'm only a
beginner at VBA). NOTE: I did post this question earlier but I did not word it clearly plus I am including an example. I have a workbook with 2 tabs, the first tab call "applications" lists all the applications our business has (ie 400 applications) and the second tab called "projects" contains a list of all projects for the entire year for the applications (ie 1000 projects). The application name from the Applications tab will be "embedded" within the project name on the Projects tab. Note: there will often be more than 1 project on the go for any given application. On the Projects tab, I would like to create a new column that can identify which application it is for by somehow being able to compare the application names on the Applications tab to the project names on the Projects tabl. The problem is trying to use wildcards without actually typing the wildcard itself into the function. I've trying using vlookup, Index, match and search without luck. For example, there may be 3 projects on the go for an application called ELM but the word ELM could be found anywhere in a project name. In the new column, I would need to identify all 3 projects and so on for all the other applications. Here is a mockup of the Applications tab: APPLICATION............ Year......... Owner BIF...........................2007......... John Smith BLAN........................2007......... Mary Smith C3............................2007.........Pete Smith CBC.........................2007......... Jerry Smith CCO.........................2007......... Abby Smith ELM..........................2007......... Joe Smith LOANS......................2007......... Jane Smith Here is a mockup of the Projects tab with the list of projects (and a new column that needs the application to be populated from the first tab): PROJECT NAME..........................PROJECT MANAGER.....APPLICATION TEST ELM PRINTING PROBLEM.... John Doe......................? CICS SUPPORT..........................Jane Doe......................? NEW CBC FORMS PROCESSES.... Mary Doe......................? ELM BATCH ENHANCEMENTS...... Joe Doe........................? S/E FOR DIY BUSINESS..............Jim Doe........................? SOFTWARE UPGRADE FOR ELM... Janet Doe.....................? CBC SUPPORT...........................Lil Doe.........................? MISC TASKS FOR JIF..................Paul Doe......................? |
Wildcard lookup
Source matching list is assumed in sheet: Applications, within A2:A8
In sheet: Projects, Project names are running in A2 down Put in C2, then array-enter the formula, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER: =IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(Applications!$ A$2:$A$8,A2)),0)),"",INDEX(Applications!$A$2:$A$8, MATCH(TRUE,ISNUMBER(SEARCH(Applications!$A$2:$A$8, A2)),0))) Copy C2 down as far as required Replace SEARCH with FIND in the expression if you want it stricter, case-sensitive. SEARCH is not case sensitive. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MLK" wrote: I'm not sure if this is possible with excel functions (and I'm only a beginner at VBA). NOTE: I did post this question earlier but I did not word it clearly plus I am including an example. I have a workbook with 2 tabs, the first tab call "applications" lists all the applications our business has (ie 400 applications) and the second tab called "projects" contains a list of all projects for the entire year for the applications (ie 1000 projects). The application name from the Applications tab will be "embedded" within the project name on the Projects tab. Note: there will often be more than 1 project on the go for any given application. On the Projects tab, I would like to create a new column that can identify which application it is for by somehow being able to compare the application names on the Applications tab to the project names on the Projects tabl. The problem is trying to use wildcards without actually typing the wildcard itself into the function. I've trying using vlookup, Index, match and search without luck. For example, there may be 3 projects on the go for an application called ELM but the word ELM could be found anywhere in a project name. In the new column, I would need to identify all 3 projects and so on for all the other applications. Here is a mockup of the Applications tab: APPLICATION............ Year......... Owner BIF...........................2007......... John Smith BLAN........................2007......... Mary Smith C3............................2007.........Pete Smith CBC.........................2007......... Jerry Smith CCO.........................2007......... Abby Smith ELM..........................2007......... Joe Smith LOANS......................2007......... Jane Smith Here is a mockup of the Projects tab with the list of projects (and a new column that needs the application to be populated from the first tab): PROJECT NAME..........................PROJECT MANAGER.....APPLICATION TEST ELM PRINTING PROBLEM.... John Doe......................? CICS SUPPORT..........................Jane Doe......................? NEW CBC FORMS PROCESSES.... Mary Doe......................? ELM BATCH ENHANCEMENTS...... Joe Doe........................? S/E FOR DIY BUSINESS..............Jim Doe........................? SOFTWARE UPGRADE FOR ELM... Janet Doe.....................? CBC SUPPORT...........................Lil Doe.........................? MISC TASKS FOR JIF..................Paul Doe......................? |
Wildcard lookup
Thanks Max! You are the best! This works perfectly. I have struggled for
days on this. Much appreciated. Thanks again, Mary-Lou "Max" wrote: Source matching list is assumed in sheet: Applications, within A2:A8 In sheet: Projects, Project names are running in A2 down Put in C2, then array-enter the formula, ie press CTRL+SHIFT+ENTER instead of just pressing ENTER: =IF(ISNA(MATCH(TRUE,ISNUMBER(SEARCH(Applications!$ A$2:$A$8,A2)),0)),"",INDEX(Applications!$A$2:$A$8, MATCH(TRUE,ISNUMBER(SEARCH(Applications!$A$2:$A$8, A2)),0))) Copy C2 down as far as required Replace SEARCH with FIND in the expression if you want it stricter, case-sensitive. SEARCH is not case sensitive. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MLK" wrote: I'm not sure if this is possible with excel functions (and I'm only a beginner at VBA). NOTE: I did post this question earlier but I did not word it clearly plus I am including an example. I have a workbook with 2 tabs, the first tab call "applications" lists all the applications our business has (ie 400 applications) and the second tab called "projects" contains a list of all projects for the entire year for the applications (ie 1000 projects). The application name from the Applications tab will be "embedded" within the project name on the Projects tab. Note: there will often be more than 1 project on the go for any given application. On the Projects tab, I would like to create a new column that can identify which application it is for by somehow being able to compare the application names on the Applications tab to the project names on the Projects tabl. The problem is trying to use wildcards without actually typing the wildcard itself into the function. I've trying using vlookup, Index, match and search without luck. For example, there may be 3 projects on the go for an application called ELM but the word ELM could be found anywhere in a project name. In the new column, I would need to identify all 3 projects and so on for all the other applications. Here is a mockup of the Applications tab: APPLICATION............ Year......... Owner BIF...........................2007......... John Smith BLAN........................2007......... Mary Smith C3............................2007.........Pete Smith CBC.........................2007......... Jerry Smith CCO.........................2007......... Abby Smith ELM..........................2007......... Joe Smith LOANS......................2007......... Jane Smith Here is a mockup of the Projects tab with the list of projects (and a new column that needs the application to be populated from the first tab): PROJECT NAME..........................PROJECT MANAGER.....APPLICATION TEST ELM PRINTING PROBLEM.... John Doe......................? CICS SUPPORT..........................Jane Doe......................? NEW CBC FORMS PROCESSES.... Mary Doe......................? ELM BATCH ENHANCEMENTS...... Joe Doe........................? S/E FOR DIY BUSINESS..............Jim Doe........................? SOFTWARE UPGRADE FOR ELM... Janet Doe.....................? CBC SUPPORT...........................Lil Doe.........................? MISC TASKS FOR JIF..................Paul Doe......................? |
Wildcard lookup
Glad it helped, Mary-Lou.
Thanks for the feedback -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MLK" wrote in message ... Thanks Max! You are the best! This works perfectly. I have struggled for days on this. Much appreciated. Thanks again, Mary-Lou |
Wildcard lookup
You could also do this with a non CSE formula
=IF(SUMPRODUCT(1-ISERR(SEARCH(applications!A$2:A$8,A2))),LOOKUP(2^1 5,FIND(applications!A$2:A$8,A2),applications!A$2:A $8),"") "Max" wrote: Glad it helped, Mary-Lou. Thanks for the feedback -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MLK" wrote in message ... Thanks Max! You are the best! This works perfectly. I have struggled for days on this. Much appreciated. Thanks again, Mary-Lou |
Wildcard lookup
.....note I mixed and matched FIND and SEARCH in the above, it should be
either FIND twice or SEARCH twice....... "daddylonglegs" wrote: You could also do this with a non CSE formula =IF(SUMPRODUCT(1-ISERR(SEARCH(applications!A$2:A$8,A2))),LOOKUP(2^1 5,FIND(applications!A$2:A$8,A2),applications!A$2:A $8),"") "Max" wrote: Glad it helped, Mary-Lou. Thanks for the feedback -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "MLK" wrote in message ... Thanks Max! You are the best! This works perfectly. I have struggled for days on this. Much appreciated. Thanks again, Mary-Lou |
| All times are GMT +1. The time now is 07:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com