|
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 |
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 |
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 |
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 |
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 |
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 |
Search multiple text criteria in one string - Help?
Bernie,
I understand. There are, as of now, about 260 unique entries, some have the words trvl, or travel, Baggage, Meals, food, bevergs, or Travel, inside the rest of its text. You know, when users come up with their own descriptions, misspellings, etc. and a pivot table can only take me so far. I came up with 12 categories that are needed at a minimum, to run another report. Travel only takes up 4, I just thought I'd start with the "easy one". 3 keywords per category didn't seem like too much, or even fool-proof, but it was a start. I'm sure there's a way to check E21 and see if it contains ANY of the 48 keywords, so far, that I came up with. Nooooobody knoooows my sorrooooow...... "Bernie Deitrick" wrote: 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 |
Search multiple text criteria in one string - Help?
Pilar,
A formula like this will tell you if one or more appears (TRUE) or none (FALSE): =SUMPRODUCT(ISNUMBER(FIND($E$2:$G$20,E21))*1)0 where E2:G20 has all the values that you a looking for within the string in cell E21. But the problem is one of exactitude, or lack thereof... Bernie "Pilar Morales" wrote in message ... Bernie, I understand. There are, as of now, about 260 unique entries, some have the words trvl, or travel, Baggage, Meals, food, bevergs, or Travel, inside the rest of its text. You know, when users come up with their own descriptions, misspellings, etc. and a pivot table can only take me so far. I came up with 12 categories that are needed at a minimum, to run another report. Travel only takes up 4, I just thought I'd start with the "easy one". 3 keywords per category didn't seem like too much, or even fool-proof, but it was a start. I'm sure there's a way to check E21 and see if it contains ANY of the 48 keywords, so far, that I came up with. Nooooobody knoooows my sorrooooow...... "Bernie Deitrick" wrote: 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 |
| All times are GMT +1. The time now is 01:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com