| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search multiple text in one string
Greetings,
I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search multiple text in one string
Pilar,
How about different AND better.... Simply make a table with the unique keywords listed down a column, and then categorize them: Keywords Category Per Diem 3 Travel Diem Lodging 3 Travel Diem 3 Travel etc, etc Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match) HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Greetings, I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search multiple text in one string
Bernie, thank you for your reply. Does this mean I need to create 48 (12X4)
VLOOKUPs nested in 12 IF functions? I'm not sure how to implement your solution... I'm searching for the keywords on table, which are then found in the first column of a pivot table. Thank you for your help, Pilar "Bernie Deitrick" wrote: Pilar, How about different AND better.... Simply make a table with the unique keywords listed down a column, and then categorize them: Keywords Category Per Diem 3 Travel Diem Lodging 3 Travel Diem 3 Travel etc, etc Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match) HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Greetings, I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search multiple text in one string
Bernie, thank you for your reply. Does this mean I need to create 48 (12X4)
VLOOKUPs nested in 12 IF functions? I'm not sure how to implement your solution... No. You would use one IF/VLOOKUP combination along the lines of =IF(E21<"",VLOOKUP(E21,Table,2,False),"") Where table is the address of your new table, like $H$1:$I$45 So, if "Per Diem" is in E21, the formula would return "3 Travel" Note, that it is better if you type the formula in directly rather than click on the cell in the pivot table to create a link: that will fire the GetPivotData, which doesn't copy well... HTH, Bernie MS Excel MVP I'm searching for the keywords on table, which are then found in the first column of a pivot table. Thank you for your help, Pilar "Bernie Deitrick" wrote: Pilar, How about different AND better.... Simply make a table with the unique keywords listed down a column, and then categorize them: Keywords Category Per Diem 3 Travel Diem Lodging 3 Travel Diem 3 Travel etc, etc Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match) HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Greetings, I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search multiple text criteria in one string - Help?
Bernie, I think I know where the confusion is.. Hear me out...
I need to find the keyword (in table) inside the text in E21 first. E21 (or in the whole E column values) may say something like: "AirFare transactions" or "Direct Travel" or "other non-travel"(which is not travel, but that's another story... It would look something like: (of course this won't work...) =IF(ColumnEitem<"",VLOOKUP(AllKeywordsinCcol, ColumnEpivotItem,returnmyCategoryinDcol,False),"") Should I post a new thread, and try to explain the situation better? Not sure where it's ambiguous... except I'm not looking at exact matches, but text (keyword) within larger text (in E21+column) so I can apply a category that will update once the pivot table is updated. Maybe 48 nested IF statements won't be so bad... How many can 2007 handle? Thank you for your time, Pilar "Bernie Deitrick" wrote: Bernie, thank you for your reply. Does this mean I need to create 48 (12X4) VLOOKUPs nested in 12 IF functions? I'm not sure how to implement your solution... No. You would use one IF/VLOOKUP combination along the lines of =IF(E21<"",VLOOKUP(E21,Table,2,False),"") Where table is the address of your new table, like $H$1:$I$45 So, if "Per Diem" is in E21, the formula would return "3 Travel" Note, that it is better if you type the formula in directly rather than click on the cell in the pivot table to create a link: that will fire the GetPivotData, which doesn't copy well... HTH, Bernie MS Excel MVP I'm searching for the keywords on table, which are then found in the first column of a pivot table. Thank you for your help, Pilar "Bernie Deitrick" wrote: Pilar, How about different AND better.... Simply make a table with the unique keywords listed down a column, and then categorize them: Keywords Category Per Diem 3 Travel Diem Lodging 3 Travel Diem 3 Travel etc, etc Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match) HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Greetings, I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Search multiple text criteria in one string - Help?
Pilar,
No, 48 nested formulas is not a good idea. If you make a table that lists at all the possible entries in column E: "AirFare transactions" or "Direct Travel" or "other non-travel" becomes a list of valid entries Valid Entries AirFare transactions Direct Travel other non-travel That you then categorize Valid Entries Category AirFare transactions Travel 1 Direct Travel Travel 2 other non-travel Travel 3 etc. Then you use VLOOKUP on that table to return Travel 1, Travel 2, Travel 3... If you allow your users to enter whatever they want in column E, then you will never get it to work. You can create a huge list of allowable values (the Valid Entries) and use data validation on cells in columns E to force the user to enter only from that list. HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Bernie, I think I know where the confusion is.. Hear me out... I need to find the keyword (in table) inside the text in E21 first. E21 (or in the whole E column values) may say something like: "AirFare transactions" or "Direct Travel" or "other non-travel"(which is not travel, but that's another story... It would look something like: (of course this won't work...) =IF(ColumnEitem<"",VLOOKUP(AllKeywordsinCcol, ColumnEpivotItem,returnmyCategoryinDcol,False),"") Should I post a new thread, and try to explain the situation better? Not sure where it's ambiguous... except I'm not looking at exact matches, but text (keyword) within larger text (in E21+column) so I can apply a category that will update once the pivot table is updated. Maybe 48 nested IF statements won't be so bad... How many can 2007 handle? Thank you for your time, Pilar "Bernie Deitrick" wrote: Bernie, thank you for your reply. Does this mean I need to create 48 (12X4) VLOOKUPs nested in 12 IF functions? I'm not sure how to implement your solution... No. You would use one IF/VLOOKUP combination along the lines of =IF(E21<"",VLOOKUP(E21,Table,2,False),"") Where table is the address of your new table, like $H$1:$I$45 So, if "Per Diem" is in E21, the formula would return "3 Travel" Note, that it is better if you type the formula in directly rather than click on the cell in the pivot table to create a link: that will fire the GetPivotData, which doesn't copy well... HTH, Bernie MS Excel MVP I'm searching for the keywords on table, which are then found in the first column of a pivot table. Thank you for your help, Pilar "Bernie Deitrick" wrote: Pilar, How about different AND better.... Simply make a table with the unique keywords listed down a column, and then categorize them: Keywords Category Per Diem 3 Travel Diem Lodging 3 Travel Diem 3 Travel etc, etc Then use VLOOKUP to pick up the category (use false as the 4th argument to force an exact match) HTH, Bernie MS Excel MVP "Pilar Morales" wrote in message ... Greetings, I have a pivot table where the first column holds the string to search (first value in E21) Airfare Account AirFare Transactions Car Rental Meals & Incidentals I have a table of 12 categories with about 3 keywords each: Pick List: Keywords: D E F G 3 Travel - Per Diem Diem lodging diem 4 Travel - Rental Car car mileage Ground trans 5 Travel - Airfare Air air Fare 6 Travel - Other travel D21 (cell to the left of pivot table value) should automatically categorize the item. Find is case sensitive, Search worked better... This is what I have on D21: =IFERROR(IF(SEARCH(OR($E$3,$F$3,$G$3),E21),$D$3,"c ontinue with next keywords"),$D$14) OR- Doesn't work on multiple search or find. Not sure how to do it in VBA. Brain freeze Help..please :) Different/faster/better solutions also welcome. Thanks, Pilar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search If on text string | Excel Worksheet Functions | |||
| Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
| Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
| How do I do a multiple search using key words in a text string | Excel Discussion (Misc queries) | |||
| Search string with multiple criteria | Excel Worksheet Functions | |||
Hybrid Mode
