Search multiple text in one string

[Pilar Morales]

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,"continue 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

 

[Bernie Deitrick]

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]

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]

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

 

[Pilar Morales]

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]

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]

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]

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